Mysql 简明教程
MySQL - Full Join
MySQL Full Join 通过整体连接两个表来创建一个新表。连接后的表包含了来自两个表的记录，并且在任一侧没有匹配的情况中填充空值。简而言之，全连接是一种外部连接，它合并了左右连接的结果。
MySQL Full Join creates a new table by joining two tables as a whole. The joined table contains all records from both the tables and fill in NULLs for missing matches on either side. In short, full join is a type of outer join that combines the results of both left and right joins.
MySQL Full Join
在 MySQL 中，没有提供执行全连接操作的方法。但是，我们可以模仿此操作以产生相同的结果。
In MySQL, there is no provision to perform full join operation. We can, however, imitate this operation to produce the same results.
通过执行全连接获得的结果集是通过左连接和右连接获得的结果集的并集。因此，我们可以首先从左连接和右连接操作中检索结果集，并使用 UNION 关键字组合它们。
The result-set obtained from performing full join is a union of result-sets obtained from left join and right join. Thus, we can first retrieve result-sets from left and right join operations and combine them using the UNION keyword.
但是，此方法仅适用于不存在重复记录的情况。如果我们想包括重复行，则最好使用 UNION ALL 关键字组合结果集。
But, this method only works for cases where duplicate records are non-existent. If we want to include the duplicate rows, using UNION ALL keyword to combine the result-sets is preferred.
Syntax
以下是模拟全连接的基本语法：
Following is the basic syntax to emulate 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;Example
在此示例中，我们使用 UNION 或 UNION ALL 关键字模拟全连接操作。首先，让我们使用以下查询创建名为 CUSTOMERS 的表：
In this example, we are imitating the full join operation using UNION or UNION ALL keyword. First, let us create a table named CUSTOMERS using the following query −;
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 语句向该表中插入值，如下所示：
Now insert values into this table using the INSERT statement as follows −
让我们创建另一个名为 ORDERS 的表，其中包含所做的订单和下单日期的详细信息。
Let us create another table ORDERS, containing the details of orders made and the date they are made on.
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2),
);使用 INSERT 语句像下面这样向该表中插入值：
Using the INSERT statement, insert values into this table as follows −
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);该表显示如下：
The table is displayed as follows −
Full Join Query −
Full Join Query −
通过执行以下查询，我们将产生两个表 CUSTOMERS 和 ORDERS 的并集。
On executing the following query, we will produce the union of two tables CUSTOMERS and ORDERS.
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
使用连接，我们默认会使用 ON 子句过滤记录。假设还有要根据某个条件过滤记录的要求，我们可以在连接中使用 WHERE 子句。
With Joins, we are filtering records using the ON clause, by default. Let us suppose there is a further requirement to filter records based on a certain condition, we can make use of WHERE clause with the Joins.
Syntax
当与 WHERE 子句一起使用时，Full Join 的语法如下：
The syntax of Full Join when used with WHERE clause is given below −
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE conditionExample
考虑以前的两个表 CUSTOMERS 和 ORDERS，并使用以下 Full Join 查询连接它们，通过 WHERE 子句应用一些约束。
Consider the previous two tables CUSTOMERS and ORDERS, and join them using the following Full Join query by applying some constraints using the WHERE clause.
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;