1.INNER JOIN(交集)[内连接]
在 SQL 中,INNER JOIN
是一种用于从两个或多个表中检索数据的方法,它会返回两个表中具有匹配值的行。换句话说,只有那些在被连接的表中都存在匹配行的数据才会出现在结果集中。
何时使用 INNER JOIN
当你想要从两个或更多表中获取数据,并且这些表之间有一个共同的列(通常是外键关系),你可以使用 INNER JOIN
来实现。这通常用于关联具有相同数据(如主键或外键)的表。
语法
INNER JOIN
的基本语法如下:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
这里的 ON
子句定义了连接条件,即哪些列应该相互匹配。
示例
让我们通过一个例子来更好地理解 INNER JOIN
。
假设我们有两个表,一个是 Customers
,另一个是 Orders
。Customers
表包含客户的详细信息,而 Orders
表包含订单的详细信息。Customers
表中的 CustomerID
是 Orders
表中的外键,用来关联这两个表。
Customers
表:
+----+-----------+-------------+---------+
| ID | FirstName | LastName | Address |
+----+-----------+-------------+---------+
| 1 | John | Doe | Street1 |
| 2 | Jane | Smith | Street2 |
| 3 | Mike | Johnson | Street3 |
+----+-----------+-------------+---------+
Orders
表:
+----+-------------+----------+
| ID | OrderNumber | Customer |
+----+-------------+----------+
| 1 | 1001 | 1 |
| 2 | 1002 | 2 |
| 3 | 1003 | 3 |
| 4 | 1004 | 1 |
+----+-------------+----------+
在这个例子中,Orders
表中的 Customer
列是 Customers
表中的 ID
的外键。
如果我们想要获取每个客户的订单详情,我们可以使用 INNER JOIN
如下:
SELECT Customers.FirstName, Customers.LastName, Orders.OrderNumber
FROM Customers
INNER JOIN Orders
ON Customers.ID = Orders.Customer;
这个查询将返回:
+-----------+-------------+-------------+
| FirstName | LastName | OrderNumber |
+-----------+-------------+-------------+
| John | Doe | 1001 |
| Jane | Smith | 1002 |
| Mike | Johnson | 1003 |
| John | Doe | 1004 |
+-----------+-------------+-------------+
这里的结果只包含了 Customers
和 Orders
表中匹配的行。例如,John Doe 在 Orders
表中有两条记录(OrderNumber 1001 和 1004),所以他在这张结果表中也出现了两次。
使用场景
INNER JOIN
常用于:
- 连接两个或多个表以检索相关联的数据:当一个表的主键作为另一个表的外键时,
INNER JOIN
可以用来连接这些表并获取相关的记录。 - 过滤非匹配的记录:只返回两个表中都有对应数据的行。
- 多表连接:在更复杂的情况下,可以使用多个
INNER JOIN
语句来连接三个或更多的表。
总结
INNER JOIN
是一种非常常用的 SQL 连接类型,它可以帮助你结合多个表中的数据,只返回那些在所有参与的表中都有匹配项的行。这对于构建报告、分析数据以及任何需要合并不同表中的信息的应用都非常有用。
2.LEFT JOIN(左全及左右交集), RIGHT JOIN(右全及左右交集)【外连接】
在 SQL 中,LEFT JOIN
和 RIGHT JOIN
是两种常用的连接类型,它们分别用于从两个表中检索数据,并且保留左侧表或右侧表中的所有行。
2.1 LEFT JOIN
LEFT JOIN
(左连接)会返回所有左侧表中的行,并且返回右侧表中匹配的行。如果右侧表中没有匹配的行,则结果集中的右侧表的列将包含 NULL
。
语法
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
示例
假设我们有两个表:Customers
和 Orders
。
Customers
表:
+----+-----------+-------------+---------+
| ID | FirstName | LastName | Address |
+----+-----------+-------------+---------+
| 1 | John | Doe | Street1 |
| 2 | Jane | Smith | Street2 |
| 3 | Mike | Johnson | Street3 |
| 4 | Sarah | White | Street4 |
+----+-----------+-------------+---------+
Orders
表:
+----+-------------+----------+
| ID | OrderNumber | Customer |
+----+-------------+----------+
| 1 | 1001 | 1 |
| 2 | 1002 | 2 |
| 3 | 1003 | 3 |
| 4 | 1004 | 1 |
+----+-------------+----------+
如果我们使用 LEFT JOIN
来连接这两个表:
SELECT Customers.ID, Customers.FirstName, Customers.LastName, Orders.OrderNumber
FROM Customers
LEFT JOIN Orders
ON Customers.ID = Orders.Customer;
结果将是:
+----+-----------+-------------+-------------+
| ID | FirstName | LastName | OrderNumber |
+----+-----------+-------------+-------------+
| 1 | John | Doe | 1001 |
| 1 | John | Doe | 1004 |
| 2 | Jane | Smith | 1002 |
| 3 | Mike | Johnson | 1003 |
| 4 | Sarah | White | NULL |
+----+-----------+-------------+-------------+
在这个结果集中,Customers
表中的所有行都被包含进来。对于 Customers
表中没有匹配的行(例如 Sarah
),Orders
表中的列将包含 NULL
。
2.2 RIGHT JOIN
RIGHT JOIN
(右连接)则相反,它会返回所有右侧表中的行,并且返回左侧表中匹配的行。如果左侧表中没有匹配的行,则结果集中的左侧表的列将包含 NULL
。
语法
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
示例
使用同样的 Customers
和 Orders
表:
SELECT Customers.ID, Customers.FirstName, Customers.LastName, Orders.OrderNumber
FROM Customers
RIGHT JOIN Orders
ON Customers.ID = Orders.Customer;
结果将是:
+----+-----------+-------------+-------------+
| ID | FirstName | LastName | OrderNumber |
+----+-----------+-------------+-------------+
| 1 | John | Doe | 1001 |
| 2 | Jane | Smith | 1002 |
| 3 | Mike | Johnson | 1003 |
| 1 | John | Doe | 1004 |
+----+-----------+-------------+-------------+
2.3. 左连接与右连接的对称性
值得注意的是,LEFT JOIN
和 RIGHT JOIN
是对称的,也就是说,如果你交换左右表的位置,结果将是对称的。例如:
SELECT Customers.ID, Customers.FirstName, Customers.LastName, Orders.OrderNumber
FROM Customers
RIGHT JOIN Orders
ON Customers.ID = Orders.Customer;
等价于:
SELECT Orders.Customer, Customers.FirstName, Customers.LastName, Orders.OrderNumber
FROM Orders
LEFT JOIN Customers
ON Orders.Customer = Customers.ID;
使用场景
- LEFT JOIN:当你希望保留左侧表中的所有行,并且展示右侧表中的匹配行(如果没有匹配,则显示
NULL
)。 - RIGHT JOIN:当你希望保留右侧表中的所有行,并且展示左侧表中的匹配行(如果没有匹配,则显示
NULL
)。
总结
LEFT JOIN
和 RIGHT JOIN
是 SQL 中非常有用的连接类型,它们可以帮助你在连接两个表时保留一侧表中的所有行。具体选择哪种连接类型取决于你希望保留哪一侧表中的所有行。如果你不确定应该使用 LEFT JOIN
还是 RIGHT JOIN
,可以考虑使用 INNER JOIN
来仅获取两个表中的匹配行,或者使用 FULL JOIN
(如果可用)来获取两个表中的所有行。
3.FULL JOIN(OUTER JOIN)【左右全集及交集】
MySQL 本身并没有直接支持 FULL JOIN
(全外连接)的语法。然而,你可以通过其他方式来模拟 FULL JOIN
的效果。通常有两种方法来实现这一点:
- 使用
UNION
和两个LEFT JOIN
或RIGHT JOIN
- 使用
UNION ALL
和两个LEFT JOIN
或RIGHT JOIN
方法一:使用 UNION
和两个 LEFT JOIN
或 RIGHT JOIN
这种方法使用两次 LEFT JOIN
或 RIGHT JOIN
,然后通过 UNION
将结果合并。这种方法可能会导致重复行,因此需要使用 UNION
而不是 UNION ALL
来去除重复行。
示例:
假设有两个表 table1
和 table2
,我们需要基于 common_column
进行 FULL JOIN
。
表 table1
:
+---------+-----------------+
| id | common_column |
+---------+-----------------+
| 1 | A |
| 2 | B |
| 3 | C |
+---------+-----------------+
表 table2
:
+---------+-----------------+
| id | common_column |
+---------+-----------------+
| 4 | B |
| 5 | D |
+---------+-----------------+
使用 LEFT JOIN
和 RIGHT JOIN
并通过 UNION
合并:
(SELECT t1.id AS t1_id, t1.common_column, t2.id AS t2_id
FROM table1 t1
LEFT JOIN table2 t2 ON t1.common_column = t2.common_column)
UNION
(SELECT t1.id AS t1_id, t1.common_column, t2.id AS t2_id
FROM table2 t2
LEFT JOIN table1 t1 ON t1.common_column = t2.common_column)
结果:
+--------+-----------------+--------+
| t1_id | common_column | t2_id |
+--------+-----------------+--------+
| 1 | A | NULL |
| 2 | B | 4 |
| 3 | C | NULL |
| 4 | B | 2 |
| 5 | D | NULL |
+--------+-----------------+--------+
4.CROSS JOIN(笛卡尔集)
在 SQL 中,CROSS JOIN
(交叉连接)是一种特殊的连接类型,它会产生两个表中所有行的笛卡尔积(Cartesian product),即第一个表中的每一行与第二个表中的每一行进行配对。
什么是交叉连接?
当执行 CROSS JOIN
时,如果表 A
有 m
行,表 B
有 n
行,那么结果集将包含 m * n
行。每个结果行都是由 A
表的一行和 B
表的一行组合而成的。
语法
CROSS JOIN
的语法如下:
SELECT *
FROM table1
CROSS JOIN table2;
或者,由于 CROSS JOIN
的关键字 CROSS
可以省略,因此也可以写作:
SELECT *
FROM table1
JOIN table2;
示例
假设我们有两个表:orders
和 items
,分别包含以下数据:
表 orders
:
+----+----------+--------+
| id | order_no | status |
+----+----------+--------+
| 1 | 1001 | open |
| 2 | 1002 | closed |
+----+----------+--------+
表 items
:
+----+-----------------+--------+
| id | item_name | price |
+----+-----------------+--------+
| 1 | Laptop | 1000 |
| 2 | Smartphone | 800 |
| 3 | Headphones | 200 |
+----+-----------------+--------+
如果我们执行 CROSS JOIN
:
SELECT o.id AS order_id, o.order_no, i.id AS item_id, i.item_name
FROM orders o
CROSS JOIN items i;
结果将是:
+----------+----------+---------+------------+
| order_id | order_no | item_id | item_name |
+----------+----------+---------+------------+
| 1 | 1001 | 1 | Laptop |
| 1 | 1001 | 2 | Smartphone |
| 1 | 1001 | 3 | Headphones |
| 2 | 1002 | 1 | Laptop |
| 2 | 1002 | 2 | Smartphone |
| 2 | 1002 | 3 | Headphones |
+----------+----------+---------+------------+
这里,表 orders
有 2 行,表 items
有 3 行,所以结果集共有 2 * 3 = 6
行。
使用场景
CROSS JOIN
主要有以下几个用途:
- 生成组合数据:当你需要生成所有可能的组合数据时,可以使用
CROSS JOIN
。 - 测试目的:在测试环境中,为了生成测试数据,可以使用
CROSS JOIN
来生成大量的测试组合。 - 数据填充:在某些情况下,为了填充数据,可以使用
CROSS JOIN
生成所有可能的组合。
注意事项
- 性能问题:对于大型表,
CROSS JOIN
可能会导致巨大的结果集,从而影响性能。因此,在生产环境中应谨慎使用。 - 明确意图:在实际应用中,很少需要生成笛卡尔积。如果确实需要生成组合数据,请确保这是有意为之,并且理解其后果。
示例代码
以下是一个具体的示例,展示如何使用 CROSS JOIN
生成所有可能的订单和商品组合:
-- 创建表 orders
CREATE TABLE orders (
id INT,
order_no INT,
status VARCHAR(10)
);
-- 插入数据
INSERT INTO orders (id, order_no, status)
VALUES (1, 1001, 'open'), (2, 1002, 'closed');
-- 创建表 items
CREATE TABLE items (
id INT,
item_name VARCHAR(50),
price INT
);
-- 插入数据
INSERT INTO items (id, item_name, price)
VALUES (1, 'Laptop', 1000), (2, 'Smartphone', 800), (3, 'Headphones', 200);
-- 执行 CROSS JOIN
SELECT o.id AS order_id, o.order_no, i.id AS item_id, i.item_name
FROM orders o
CROSS JOIN items i;
这个查询将生成表 orders
和表 items
的所有可能组合。
总结
CROSS JOIN
会产生两个表的笛卡尔积,即第一个表中的每一行与第二个表中的每一行进行配对。在实际应用中,CROSS JOIN
应该谨慎使用,特别是在处理大型数据集时。如果你确实需要生成所有可能的组合数据,CROSS JOIN
是一个有效的工具。