SQL中的各种JOIN

概述

图解各种sql，记住下面这个图即可以记住全部内容了

详细介绍

JOIN类型	用法说明	注意事项
INNER JOIN	返回满足连接条件的交集，即返回同时存在于两个表中的行 简为JOIN	- 连接条件需要明确指定 - 如果有多个连接条件，可以使用AND或者OR来组合 - 如果连接条件不满足，对应的行将被排除在结果中
LEFT JOIN	返回左表中的所有行，以及右表中满足连接条件的匹配行。如果右表中没有匹配的行，则为NULL	- 左表中的所有行都会被返回，无论是否有匹配的行 - 右表中没有匹配的行将显示为NULL - 使用LEFT JOIN时，要注意右表可能出现的NULL值，需要进行处理
RIGHT JOIN	返回右表中的所有行，以及左表中满足连接条件的匹配行。如果左表中没有匹配的行，则为NULL	- 右表中的所有行都会被返回，无论是否有匹配的行 - 左表中没有匹配的行将显示为NULL - 使用RIGHT JOIN时，要注意左表可能出现的NULL值，需要进行处理
FULL JOIN	返回左表和右表中的所有行，并将满足连接条件的行进行合并。如果某个表中没有匹配的行，则为NULL	- 返回的结果包含了两个表中的所有行，无论是否有匹配的行 - 使用FULL JOIN时，要注意可能出现的NULL值，需要进行处理
CROSS JOIN	返回两个表中的所有行的笛卡尔积，即左表中的每一行都与右表中的每一行进行组合	- 结果中的行数是左表的行数乘以右表的行数 - 使用CROSS JOIN时，要注意结果的行数可能会非常大，可能导致性能问题
SELF JOIN	将一个表与自身进行JOIN操作，常用于比较同一表中的不同记录	- SELF JOIN需要使用别名来区分表的副本 - 连接条件需要明确指定，通常需要使用别名来引用不同的表副本
NATURAL JOIN	根据两个表之间的相同列名自动进行JOIN操作，无需指定连接条件	- 尽量避免使用NATURAL JOIN，因为它依赖列名的相同性，可能导致意外的结果 - 如果表中有多列的列名相同，NATURAL JOIN可能会产生笛卡尔积效果
UNION	将两个或多个SELECT语句的结果合并为一个结果集，要求每个SELECT语句的列数和数据类型必须相同	- UNION会去除重复的行，如果想要保留重复的行，可以使用UNION ALL - UNION操作的结果集的列名和数据类型来自于第一个SELECT语句的列名和数据类型，因此确保第一个SELECT语句的列名和数据类型符合预期
INTERSECT	返回两个或多个SELECT语句的结果中共有的行，要求每个SELECT语句的列数和数据类型必须相同	- INTERSECT会去除重复的行，如果想要保留重复的行，可以使用INTERSECT ALL - INTERSECT操作的结果集的列名和数据类型来自于第一个SELECT语句的列名和数据类型，因此确保第一个SELECT语句的列名和数据类型符合预期
EXCEPT	返回第一个SELECT语句的结果中不在其他SELECT语句结果中的行，要求每个SELECT语句的列数和数据类型必须相同	- EXCEPT操作的结果集的列名和数据类型来自于第一个SELECT语句的列名和数据类型，因此确保第一个SELECT语句的列名和数据类型符合预期
APPLY	在FROM子句中使用APPLY关键字，将一个表连接到另一个表的每一行，常用于对表中的每一行应用一个表达式或函数	- APPLY操作的结果是将被连接的表的每一行与连接表的结果一起返回，可以根据需要选择APPLY的类型（CROSS APPLY还是OUTER APPLY）

注意事项：在使用JOIN操作时，需要注意以下事项：

1. 确保连接条件正确，否则可能导致错误的结果。
2. 注意处理可能出现的NULL值，尤其是在使用LEFT JOIN和RIGHT JOIN时。
3. 考虑性能问题，特别是在使用CROSS JOIN时，结果的行数可能会非常大。
4. 避免使用NATURAL JOIN，因为它依赖列名的相同性，可能导致意外的结果。
5. 确保UNION、INTERSECT和EXCEPT操作的SELECT语句的列数和数据类型相同。
6. 在使用APPLY操作时，根据需要选择APPLY的类型（CROSS APPLY还是OUTER APPLY）。

举例说明

下面是基于前面所述的语法，在一个示例数据库中使用各种JOIN类型的具体SQL语句和结果示例：

假设我们有两个表：Customers（客户）和 Orders（订单），它们的结构如下：

Customers:

CustomerID	CustomerName	ContactName	Country
1	John Smith	Peter	USA
2	Alice Brown	Kelly	UK
3	Bob Johnson	James	USA
4	Lisa Green	Emily	Canada

Orders:

OrderID	CustomerID	OrderDate	Amount
1	1	2021-01-01	100
2	1	2021-02-01	200
3	2	2021-01-15	150
4	3	2021-02-10	300
5	4	2021-03-01	250

1. INNER JOIN（内连接）：

查询每个订单的客户信息：

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate 
FROM Orders 
INNER JOIN Customers 
ON Orders.CustomerID = Customers.CustomerID;

结果：

OrderID	CustomerName	OrderDate
1	John Smith	2021-01-01
2	John Smith	2021-02-01
3	Alice Brown	2021-01-15
4	Bob Johnson	2021-02-10
5	Lisa Green	2021-03-01

2. LEFT JOIN（左连接）：

查询每个客户的所有订单信息，包括没有订单的客户：

SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
  FROM Customers 
3LEFT JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID;

结果：

CustomerName	OrderID	OrderDate
John Smith	1	2021-01-01
John Smith	2	2021-02-01
Alice Brown	3	2021-01-15
Bob Johnson	4	2021-02-10
Lisa Green	5	2021-03-01
NULL	NULL	NULL

3. RIGHT JOIN（右连接）：

查询每个订单的客户信息，包括没有客户的订单：

SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
 FROM Customers 
RIGHT JOIN Orders 4ON Customers.CustomerID =Orders.CustomerID;

结果：

CustomerName	OrderID	OrderDate
John Smith	1	2021-01-01
John Smith	2	2021-02-01
Alice Brown	3	2021-01-15
Bob Johnson	4	2021-02-10
Lisa Green	5	2021-03-01
NULL	NULL	NULL

3. FULL JOIN（全连接）

查询所有客户和订单的信息，包括没有匹配的行：

SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
FULL JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID;

结果：

CustomerName	OrderID	OrderDate
John Smith	1	2021-01-01
John Smith	2	2021-02-01
Alice Brown	3	2021-01-15
Bob Johnson	4	2021-02-10
Lisa Green	5	2021-03-01
NULL	NULL	NULL

4. CROSS JOIN（交叉连接）

查询每个客户和订单的组合，返回所有可能的组合：

SELECT Customers.CustomerName, Orders.OrderID, 
Orders.OrderDate FROM Customers 
CROSS JOIN Orders;

结果：

CustomerName	OrderID	OrderDate
John Smith	1	2021-01-01
John Smith	2	2021-02-01
John Smith	3	2021-01-15
John Smith	4	2021-02-10
John Smith	5	2021-03-01
Alice Brown	1	2021-01-01
Alice Brown	2	2021-02-01
Alice Brown	3	2021-01-15
Alice Brown	4	2021-02-10
Alice Brown	5	2021-03-01
Bob Johnson	1	2021-01-01
Bob Johnson	2	2021-02-01
Bob Johnson	3	2021-01-15
Bob Johnson	4	2021-02-10
Bob Johnson	5	2021-03-01
Lisa Green	1	2021-01-01
Lisa Green	2	2021-02-01
Lisa Green	3	2021-01-15
Lisa Green	4	2021-02-10
Lisa Green	5	2021-03-01

5. SELF JOIN（自连接）

自连接用于将表自身与其子查询结果进行连接操作。在这个例子中，我们将使用SELF JOIN来查找具有相同产品的不同客户。

SELECT A.Name AS Customer1, B.Name AS Customer, A.Product 2FROM Orders A INNER JOIN Orders B ON A.Product = B.Product WHERE A.CustomerID <> B.CustomerID;

结果：

Customer1	Customer2	Product
Alice	Bob	Apple
Bob	Alice	Apple