MySQL - 全连接

MySQL - 模拟 FULL OUTER JOIN

FULL OUTER JOIN（全外连接）结合了 LEFT JOIN（左连接）和 RIGHT JOIN（右连接）的结果集。结果集中包含两个表的所有记录。对于满足连接条件的行，两个表中的列会合并。对于不满足连接条件的行，缺失表的列将填充 NULL。

在 MySQL 中模拟 FULL JOIN

与 PostgreSQL 或 SQL Server 等其他一些 SQL 数据库不同，MySQL 没有内置的 FULL OUTER JOIN 关键字。但是，可以通过将 LEFT JOIN 和 RIGHT JOIN 与 UNION 运算符结合使用来完美模拟此操作。

语法

模拟 FULL OUTER JOIN 的标准模式是：

SELECT ...
FROM table1
LEFT JOIN table2 ON table1.common_field = table2.common_field

UNION

SELECT ...
FROM table1
RIGHT JOIN table2 ON table1.common_field = table2.common_field;

UNION 运算符组合了两个 SELECT 语句的结果集并移除了重复行。这很重要，因为两个表中匹配的行否则会出现两次（一次来自 LEFT JOIN，一次来自 RIGHT JOIN）。如果您想保留所有行，包括重复行，可以使用 UNION ALL，但这通常不是 FULL JOIN 模拟的预期行为。

示例：结合客户和订单

让我们考虑两个表：Customers（列出所有客户）和 Orders（列出所有订单）。有些客户可能没有下过任何订单，并且（假设性地）有些订单可能没有关联到客户。

首先，创建并填充 Customers 表：

CREATE TABLE Customers (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

INSERT INTO Customers (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

接下来，创建并填充 Orders 表。请注意，Bob（客户 ID 2）有一个订单，Charlie（ID 3）没有，而订单 103 有一个无效的客户 ID。

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2)
);

INSERT INTO Orders (order_id, customer_id, amount) VALUES
(101, 1, 50.00),
(102, 1, 75.00),
(103, 99, 200.00); -- 孤立订单

现在，让我们执行模拟的 FULL OUTER JOIN 来查看所有客户和所有订单。

SELECT c.id, c.name, o.order_id, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customer_id

UNION

SELECT c.id, c.name, o.order_id, o.amount
FROM Customers c
RIGHT JOIN Orders o ON c.id = o.customer_id;

输出

结果显示了所有客户和所有订单。Charlie 没有订单数据（NULL），而孤立订单没有客户数据（NULL）。

id	name	order_id	amount
1	Alice	101	50.00
1	Alice	102	75.00
2	Bob	NULL	NULL
3	Charlie	NULL	NULL
NULL	NULL	103	200.00

过滤 FULL JOIN 的结果

在过滤外部连接的结果时，请注意条件放置的位置。在 WHERE 子句中对“外部”表放置筛选条件可能会无意中将查询转换为 INNER JOIN（内连接）。

常见陷阱

例如，如果您想查看所有客户及其订单，但只针对金额大于 60.00 的订单，则此查询是错误的：

-- 错误：这将表现为 INNER JOIN
SELECT c.id, c.name, o.order_id, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customer_id
WHERE o.amount > 60.00;

WHERE o.amount > 60.00 条件会过滤掉 o.amount 为 NULL 的任何行，从而有效地删除了所有没有匹配订单的客户。

正确方法

为了正确过滤，条件应该成为 ON 子句的一部分，或者在连接后使用子查询或公用表表达式（CTE）应用。

-- 正确：将过滤器移动到 ON 子句
SELECT c.id, c.name, o.order_id, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customer_id AND o.amount > 60.00

UNION

SELECT c.id, c.name, o.order_id, o.amount
FROM Customers c
RIGHT JOIN Orders o ON c.id = o.customer_id AND o.amount > 60.00;

输出

这正确地返回了所有客户，只显示了 Alice 的相关订单，其他客户则显示 NULL。

id	name	order_id	amount
1	Alice	102	75.00
2	Bob	NULL	NULL
3	Charlie	NULL	NULL
NULL	NULL	103	200.00