SQL的JOIN连接多表查询

前言:

数据库上课时这一部分没有听进去，前几天面试又问到了SQL，现在才发现答得简直是乱七八糟，那就趁现在好好整理一下吧，感谢面试官的包容，I really appreciate that！

0.所有的JOINS概览

1
2
3
4
5
6
7
8
INNER JOIN
LEFT JOIN
RIGHT JOIN
OUTER JOIN
LEFT JOIN EXCLUDING INNER JOIN
RIGHT JOIN EXCLUDING INNER JOIN
OUTER JOIN EXCLUDING INNER JOIN
CROSS JOIN
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
用于查询的两张表信息
TABLE_A
  PK Value
---- ----------
   1 FOX
   2 COP
   3 TAXI
   6 WASHINGTON
   7 DELL
   5 ARIZONA
   4 LINCOLN
  10 LUCENT

TABLE_B
  PK Value
---- ----------
   1 TROT
   2 CAR
   3 CAB
   6 MONUMENT
   7 PC
   8 MICROSOFT
   9 APPLE
  11 SCOTCH

1.Inner JOIN内连接

内连接

说明：相当于取两张表的交集，JOIN没有特别说明时默认就是内连接

1
2
3
4
5
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK

结果：

1
2
3
4
5
6
7
8
9
A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7

(5 row(s) affected)

2.Left JOIN左连接

左连接

说明：相当于取两张表的交集加上左表，即所有的左表数据都会被显示出来，不管有没有匹配的右表，没有的话右表部分就显示NULL

1
2
3
4
5
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK

结果：

1
2
3
4
5
6
7
8
9
10
A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
  10 LUCENT     NULL       NULL

3.Right JOIN右连接

右连接

说明：相当于取两张表的交集加上右表，即所有的右表数据都会被显示出来，不管有没有匹配的左表，没有的话左表的部分就显示NULL

1
2
3
4
5
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK

结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(8 row(s) affected)

4.OUTER JOIN外连接

外连接

说明：会返回两张表的所有的记录，相当于两张表的并集

1
2
3
4
5
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK

结果：sql server 里先显示内连接，再是右连接，最后是左连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(11 row(s) affected)

5.Left Excluding JOIN左表唯一连接

左表唯一连接

说明：会返回左表中与右表不能匹配的所有记录

1
2
3
4
5
6
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL

结果：

1
2
3
4
5
6
7
A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
  10 LUCENT     NULL       NULL
(3 row(s) affected)

6.Right Excluding JOIN右表唯一连接

右表唯一连接

说明：会返回右表中与左表不能匹配的所有记录

1
2
3
4
5
6
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL

结果：

1
2
3
4
5
6
7
8
A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(3 row(s) affected)

7.Outer Excluding JOIN非交集连接

非交集连接

说明：会返回左表和右表里没有相互关联的记录

1
2
3
4
5
6
7
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL

结果：

1
2
3
4
5
6
7
8
9
10
11
A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(6 row(s) affected)

8.Cross JOIN交叉连接

交叉连接

说明：一张表中的数据依次取出分别与另一张表中的每条数据挨个组合，最后记录数量为两张表记录数的乘积，就是笛卡尔积。

1
例如：SELECT * FROM student CROSS JOIN major

9.有关Where的多表连接查询

9.1where和inner join的区别：

1
SELECT a.ID, b.Name, b.Date FROM Customers a, Sales b WHERE a.ID = b.ID;
1
SELECT a.ID, b.Name, b.Date FROM Customers a INNER JOIN Sales b ON a.ID = b.ID;

两者的结果是一样的。

但是where是实际上是创建了两张表的笛卡尔积，然后再做筛选过滤。如果两张表各有1000条记录，就会先产生1000000个结果，然后通过正确的 ID过滤出符合1000条记录。

而使用inner join 这样数据库就只产生符合的1000条目标结果，相比之下，查询效率更高。

9.2on和where的区别：

  • 对于left join:
  • on条件是生成临时表时的条件，无论on的条件是否为真，左表的数据都会返回，只是右表数据这是都会变成null，这是Left Join的特性(Right Join同)
  • where是在临时表创建完后根据条件进行筛选
  • 即：先on 建立临时表再where进行筛选

最后的全家福

所有连接

参考文章1：https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

参考文章2：https://zhuanlan.zhihu.com/p/68136613

数据库
#SQL
SQL的JOIN连接多表查询
http://example.com/2024/01/22/网上技术学习/SQL的JOIN连接多表查询/
作者
jhxxxxx
发布于
2024年1月22日
许可协议