MySQL - 全连接



MySQL 全连接 通过将两张表整体连接起来创建一个新表。连接后的表包含两张表中的所有记录,并为缺失的匹配项填充 NULL 值。简而言之,全连接是一种外部连接,它结合了左连接和右连接的结果。

MySQL 全连接

在 MySQL 中,没有执行全连接操作的规定。但是,我们可以模拟此操作以产生相同的结果。

从执行全连接获得的结果集是左连接和右连接获得的结果集的并集。因此,我们可以首先从左连接和右连接操作中检索结果集,然后使用 UNION 关键字将它们组合起来。

但是,此方法仅适用于不存在重复记录的情况。如果我们想要包含重复行,则最好使用 UNION ALL 关键字来组合结果集。

语法

以下是模拟全连接的基本语法:

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 关键字模拟全连接操作。首先,让我们使用以下查询创建一个名为 CUSTOMERS 的表:

Open Compiler
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 语句向该表插入值,如下所示:

ID 姓名 (NAME) 年龄 (AGE) 地址 (ADDRESS) 薪水 (SALARY)
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 的表,其中包含订单详细信息及其下单日期。

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

使用 INSERT 语句,向该表插入值,如下所示:

Open Compiler
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);

该表显示如下:

OID 日期 (DATE) 客户ID (CUSTOMER_ID) 金额 (AMOUNT)
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 两张表的并集。

Open Compiler
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;

输出

生成的表如下所示:

ID 姓名 (NAME) 金额 (AMOUNT) 日期 (DATE)
1 Ramesh NULL NULL
2 Khilan 1560 2009-11-20 00:00:00
3 Kaushik 3000 2009-10-08 00:00:00
3 Kaushik 1500 2009-10-08 00:00:00
4 Chaitali 2060 2008-05-20 00:00:00
5 Hardik NULL NULL
6 Komal NULL NULL
7 Muffy NULL NULL

带 WHERE 子句的全连接

使用连接时,我们默认使用 ON 子句过滤记录。假设还需要根据特定条件过滤记录,我们可以将 WHERE 子句与连接一起使用。

语法

与 WHERE 子句一起使用的全连接语法如下:

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

示例

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

Open Compiler
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;

输出

在将 where 子句与全连接一起使用后,生成的表包含金额值大于 2000.00 的行。

ID 姓名 (NAME) 日期 (DATE) 金额 (AMOUNT)
3 Kaushik 2009-10-08 00:00:00 3000.00
4 Chaitali 2008-05-20 00:00:00 2060.00
广告