SQL中的各种连接join

发表于 更新于 分类于 阅读次数：

参考：
https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html
https://www.w3school.com.cn/sql/sql_join_left.asp
https://www.w3school.com.cn/sql/sql_join.asp
https://www.cnblogs.com/yelp/p/3761443.html
https://blog.csdn.net/qq_41973536/article/details/81081024

最常见的 JOIN 类型：

1
2
3
4
INNER JOIN（等价于 JOIN）
LEFT JOIN（等价于LEFT OUTER JOIN）
RIGHT JOIN（等价于RIGHT OUTER JOIN）
SQL FULL JOIN（等价于FULL OUTER JOIN）

其中第一种是内连接，后三种是外连接。

  • JOIN: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。（如果表中有至少一个匹配，则返回行）
  • LEFT JOIN: 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。（即使右表中没有匹配，也从左表返回所有的行）
  • RIGHT JOIN: 右连接，返回右表中所有的记录以及左表中连接字段相等的记录。（即使左表中没有匹配，也从右表返回所有的行）
  • FULL JOIN: 外连接，返回两个表中的行：left join + right join。（只要其中一个表中存在匹配，就返回行）
  • cross join: 结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

假设我们有两张表，Table A是左边的表，Table B是右边的表。

Table A:

id name
1 Google
2 淘宝
3 微博
4 Facebook

Table B:

id address
1 美国
5 中国
3 中国
6 美国

1. INNER JOIN

内连接只连接匹配的行。

语法：

1
2
3
4
5
select column_name(s)
from table 1
INNER JOIN table 2
ON
table 1.column_name=table 2.column_name

1624780519991

INNER JOIN产生的结果集，是1和2的交集。

1
2
3
select tableA.id, name, address 
from tableA join tableB
on tableA.id = tableB.id

执行以上SQL输出结果如下：

id name address
1 Google 美国
3 微博 中国

等价于

1
2
3
select tableA.id, name, address
from tableA, tableB
where tableA.id = tableB.id

2. LEFT JOIN

LEFT JOIN返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。

语法：

1
2
3
4
select column_name(s)
from table 1
LEFT JOIN table 2
ON table 1.column_name=table 2.column_name

1624780567674

LEFT JOIN产生表1的完全集，而2表中匹配的则有值，没有匹配的则以null值取代。

1
2
3
select tableA.id, name, address 
from tableA left join tableB
on tableA.id = tableB.id

执行以上SQL输出结果如下：

id name address
1 Google 美国
2 淘宝 null
3 微博 中国
4 Facebook null

注：若左表中一条记录匹配右表中多行，则都会返回

例：

"Persons" 表：

Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan St Beijing

"Orders" 表：

Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65 
1
2
3
4
5
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

结果集：

LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Bush George null
Carter Thomas 44678
Carter Thomas 77895

LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

3. RIGHT JOIN

RIGHT JOIN返回右表的全部行和左表满足ON条件的行，如果右表的行在左表中没有匹配，那么这一行左表中对应数据用NULL代替。

语法：

1
2
3
4
select column_name(s)
from table 1
RIGHT JOIN table 2
ON table 1.column_name=table 2.column_name

1624780592546

RIGHT JOIN产生表2的完全集，而1表中匹配的则有值，没有匹配的则以null值取代。

1
2
3
select tableA.id, name, address 
from tableA right join tableB
on tableA.id = tableB.id

执行以上SQL输出结果如下：

id name address
1 Google 美国
5 null 中国
3 微博 中国
6 null 美国

4. FULL OUTER JOIN

FULL JOIN 会从左表和右表中返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替。

语法：

1
2
3
4
select column_name(s)
from table 1
FULL OUTER JOIN table 2
ON table 1.column_name=table 2.column_name

1624780609082

FULL OUTER JOIN产生1和2的并集。对于没有匹配的记录，会以null做为值。

1
2
3
select tableA.id, name, address 
from tableA full outer join tableB
on tableA.id = tableB.id

执行以上SQL输出结果如下：

id name address
1 Google 美国
2 淘宝 null
3 微博 中国
4 Facebook null
5 null 中国
6 null 美国

5. join中on与where的区别

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left join时，on和where条件的区别如下： 1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：table1

id size
1 10
2 20
3 30

表2：table2

size name
10 AAA
20 BBB
20 CCC

两条SQL:

1
2
3
4
5
6
7
8
9
10
select * 
form table1 
left join table2 
on (table1.size = table2.size) 
where table2.name=’AAA’

select * 
form table1 
left join table2 
on (table1.size = table2.size and table2.name=’AAA’)

第一条SQL的过程：

1、中间表on条件: table1.size = table2.size

table1.id table1.size table2.size table2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 CCC
3 30 (null) (null)

2、再对中间表过滤 where 条件： table2.name=’AAA’

table1.id table1.size table2.size table2.name
1 10 10 AAA

第二条SQL的过程： 1、中间表on条件: table1.size = table2.size and table2.name=’AAA’ (条件不为真也会返回左表中的记录)

table1.id table1.size table2.size table2.name
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)

on条件是在left join之前先进行条件筛选，而后才对两个表格join操作。on比where起作用更早，先根据on条件进行多表的连接操作，生成一个临时表再通过where来筛选。

以上结果的关键原因在于left join,right join,full join的特殊性，不管on上的条件是否为真都会返回 left 或 right 表中的记录，full则具有left和right的特性的并集。 而inner join没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。