在操作数据库时,面对一张表时往往没什么太大问题,但实际上大多数的查询需要涉及到多张表,进行多表联查;本文主要记录了本人在学习 SQL 多表联查的一些操作。
在开始之前,我们准备了两张表,作为下面操作的对象。
id | 学号 | 姓名 | 性别 | 年龄 |
---|---|---|---|---|
1 | 210001 | A | 女 | 18 |
2 | 210002 | B | 男 | 18 |
3 | 210003 | C | 男 | 17 |
4 | 210004 | D | 女 | 19 |
5 | 210005 | E | 女 | 17 |
6 | 210006 | F | 男 | 18 |
id | 学号 | 专业 | 班级 |
---|---|---|---|
1 | 210001 | 数学 | 数 1 |
2 | 210003 | 计算机 | 计 1 |
3 | 210004 | 软件工程 | 软 2 |
4 | 210005 | 计算机 | 计 3 |
5 | 210007 | 物理 | 物 1 |
6 | 210008 | 英语 | 英 2 |
使用场景:获取两个表中指定字段满足匹配关系的记录
SELECT <select_list>
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key
查询示例:
获取每个学生的学号、姓名、性别、年龄、专业、班级信息
SELECT A.学号, A.姓名, A.性别, A.年龄, B.专业, B.班级
FROM student A
INNER JOIN class B
ON A.学号 = B.学号
-- 你也可以省去 INNER,直接写 JOIN
SELECT A.学号, A.姓名, A.性别, A.年龄, B.专业, B.班级
FROM student A
JOIN class B
ON A.学号 = B.学号
查询结果:
id | 学号 | 姓名 | 性别 | 年龄 | 专业 | 班级 |
---|---|---|---|---|---|---|
1 | 210001 | A | 女 | 18 | 数学 | 数 1 |
2 | 210003 | C | 男 | 17 | 计算机 | 计 1 |
3 | 210004 | D | 女 | 19 | 软件工程 | 软 2 |
4 | 210005 | E | 女 | 17 | 计算机 | 计 3 |
使用场景:获取左表中的所有记录,即使在右表没有对应匹配的记录
SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
查询示例:
获取每个学生的学号、姓名、性别、年龄、专业、班级信息
SELECT A.学号, A.姓名, A.性别, A.年龄, B.专业, B.班级
FROM student A
LEFT JOIN class B
ON A.学号 = B.学号
查询结果:
id | 学号 | 姓名 | 性别 | 年龄 | 专业 | 班级 |
---|---|---|---|---|---|---|
1 | 210001 | A | 女 | 18 | 数学 | 数 1 |
2 | 210002 | B | 男 | 18 | NULL | NULL |
3 | 210003 | C | 男 | 17 | 计算机 | 计 1 |
4 | 210004 | D | 女 | 19 | 软件工程 | 软 2 |
5 | 210005 | E | 女 | 17 | 计算机 | 计 3 |
6 | 210006 | F | 男 | 18 | NULL | NULL |
使用场景:获取右表中的所有记录,即使在左表没有对应匹配的记录
SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
查询示例:
获取每个学生的学号、姓名、性别、年龄、专业、班级信息
SELECT A.学号, A.姓名, A.性别, A.年龄, B.专业, B.班级
FROM student A
RIGHT JOIN class B
ON A.学号 = B.学号
查询结果:
id | 学号 | 姓名 | 性别 | 年龄 | 专业 | 班级 |
---|---|---|---|---|---|---|
1 | 210001 | A | 女 | 18 | 数学 | 数 1 |
2 | 210003 | C | 男 | 17 | 计算机 | 计 1 |
3 | 210004 | D | 女 | 19 | 软件工程 | 软 2 |
4 | 210005 | E | 女 | 17 | 计算机 | 计 3 |
5 | NULL | NULL | NULL | NULL | 物理 | 物 1 |
6 | NULL | NULL | NULL | NULL | 英语 | 英 2 |
使用场景:获取两个表中的所有行
SELECT <select_list>
FROM Table_A A
FULL JOIN Table_B B
ON A.Key = B.Key
FULL OUTER JOIN
或者 FULL JOIN
都可以
查询示例:
获取每个学生的学号、姓名、性别、年龄、专业、班级信息
SELECT A.学号, A.姓名, A.性别, A.年龄, B.专业, B.班级
FROM student A
FULL JOIN class B
ON A.学号 = B.学号
查询结果:
id | 学号 | 姓名 | 性别 | 年龄 | 专业 | 班级 |
---|---|---|---|---|---|---|
1 | 210001 | A | 女 | 18 | 数学 | 数 1 |
2 | 210002 | B | 男 | 18 | NULL | NULL |
3 | 210003 | C | 男 | 17 | 计算机 | 计 1 |
4 | 210004 | D | 女 | 19 | 软件工程 | 软 2 |
5 | 210005 | E | 女 | 17 | 计算机 | 计 3 |
6 | 210006 | F | 男 | 18 | NULL | NULL |
7 | NULL | NULL | NULL | NULL | 物理 | 物 1 |
8 | NULL | NULL | NULL | NULL | 英语 | 英 2 |
使用场景:获取左表有但右表没有关联数据的记录
SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL
使用场景:获取右表有但左表没有关联数据的记录
SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL
使用场景:获取左表和右表里没有相互关联的记录
SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL