SQL - 全连接 (Full Join)

SQL 全连接 (Full Join)

SQL Full Join 通过将两个表整体连接来创建一个新表。连接后的表包含两个表中的所有记录，对于任一边缺少的匹配项会填充 NULL 值。简而言之，full join 是一种 outer join，它结合了 left join 和 right join 的结果集。

MySQL 不支持 Full Outer Join。相反，您可以通过对 Left Join 和 Right Join 获得的结果集执行 union 操作来模拟其功能。

让我们通过下面的 Venn 图详细理解这个概念。假设我们有两个表作为两个集合（用圆圈表示）。使用 full join 得到的结果集（或新连接的表）就是这两个集合的并集。

您也可以通过对 LEFT JOIN 和 RIGHT JOIN 的结果集执行 UNION 操作来获得等效的 FULL JOIN 结果集。

语法

以下是 SQL 中 Full Join 的基本语法 −

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

示例

假设我们创建了一个名为 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 语句向该表插入值，如下所示 −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

表将创建如下 −

让我们创建另一个表 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);

表显示如下 −

以下查询在 SQL Server 中连接两个表 CUSTOMERS 和 ORDERS −

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

输出

结果表生成如下 −

使用 Full Join 连接多个表

Full Join 查询也可以用于连接不止两个表。要实现这一点，我们可以依次每次连接两个表，直到所有表都被连接在一起。

请注意，在 MySQL 数据库中，没有直接使用 FULL JOIN 关键字对多个表执行连接操作的语法。相反，需要计算每次两个表的 LEFT JOIN 和 RIGHT JOIN 的 UNION，直到所有表都被连接。

语法

使用 Full Join 连接多个表的语法如下所示 −

SELECT column1, column2, column3... 
FROM table1
FULL JOIN table2
ON condition_1
FULL JOIN table3
ON condition_2
....
....
FULL JOIN tableN
ON condition_N;

示例

为了演示 Full Join，我们考虑之前创建的示例表 CUSTOMERS 和 ORDERS，并使用以下查询创建一个名为 EMPLOYEE 的新表 −

CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);

现在，我们可以使用 INSERT 语句向这个空表中插入值，如下所示 −

INSERT INTO EMPLOYEE VALUES
(102, 'SARIKA', 4500),
(100, 'ALEKHYA', 3623),
(101, 'REVATHI', 1291),
(103, 'VIVEK', 3426);

创建的 EMPLOYEE 表将如下所示 −

让我们使用以下 full join 查询连接这三个表 −

SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME 
FROM CUSTOMERS 
FULL JOIN ORDERS 
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID 
FULL JOIN EMPLOYEE 
ON ORDERS.OID = EMPLOYEE.EID; 

通过这个查询，我们将显示客户的 id 和 name，以及订单日期和销售该物品的员工姓名。

输出

结果表如下所示 −

带 WHERE 子句的 Full Join

连接操作默认使用 ON 子句来过滤记录。假设有进一步需求需要根据特定条件/约束过滤这些记录，我们也可以在连接中使用 WHERE 子句。

语法

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

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

示例

考虑之前的两个表 CUSTOMERS 和 ORDERS，并使用以下 Full Join 查询连接它们，同时使用 WHERE 子句应用一些约束。

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

输出

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