MySQL - 全连接

MySQL Full Join 通过将两个表整体连接来创建一个新表。连接后的表包含两个表中的所有记录，并在任一侧缺少匹配时填充 NULL。简而言之，full join 是一种 outer join 类型，它结合了 left join 和 right join 的结果。

MySQL 全连接

MySQL 中没有直接支持 full join 操作。不过，我们可以通过模拟此操作来产生相同的结果。

执行 full join 得到的结果集是 left join 和 right join 结果集的 union。因此，我们可以先获取 left join 和 right join 操作的结果集，然后使用 UNION 关键字将它们组合。

但是，这种方法仅适用于没有重复记录的情况。如果要包含重复行，则优先使用 UNION ALL 关键字来组合结果集。

语法

以下是模拟 Full Join 的基本语法 −

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field

[UNION | UNION ALL]

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

示例

在本示例中，我们使用 UNION 或 UNION ALL 关键字模拟 full join 操作。首先，使用以下查询创建一个名为 CUSTOMERS 的表 −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

现在使用 INSERT 语句向该表插入值，如下所示 −

让我们创建另一个表 ORDERS，其中包含订单详情和订单日期。

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
);

使用 INSERT 语句向该表插入值，如下所示 −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

表显示如下 −

全连接查询 −

执行以下查询，将产生两个表 CUSTOMERS 和 ORDERS 的 union。

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

输出

结果表生成如下 −

Full Join with WHERE Clause

在使用 Joins 时，我们默认使用 ON 子句来过滤记录。假设有进一步的需求，需要根据特定条件过滤记录，我们可以使用 WHERE 子句与 Joins 结合使用。

Syntax

Full Join 与 WHERE 子句结合使用时的语法如下 −

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

Example

考虑之前的两个表 CUSTOMERS 和 ORDERS，使用以下 Full Join 查询将它们连接起来，并通过 WHERE 子句应用一些约束条件。

SELECT ID, NAME, DATE, AMOUNT 
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00

UNION 

SELECT ID, NAME, DATE, AMOUNT 
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

Output

应用 WHERE 子句与 full join 后的结果表包含金额值大于 2000.00 的行 −