Sql 简明教程

SQL - Full Join

The SQL Full Join

SQL Full Join 通过整体连接两个表来创建一个新表。连接表包含来自两个表的全部记录，并用 NULL 值填充任一侧缺少的匹配项。简而言之，满连接是一种外部连接，它将左连接和右连接的结果集组合在一起。

SQL Full Join creates a new table by joining two tables as a whole. The joined table contains all records from both the tables and fills NULL values for missing matches on either side. In short, full join is a type of outer join that combines the result-sets of both left and right joins.

让我们通过以下韦恩图详细了解这个概念。假设我们有两个表作为两组（由圆圈表示）。使用满连接获取的结果集（或新连接表）正是这两个集合的并集。

Let us understand this concept in detail with the help of a Venn diagram below. Assume that we have two tables as two sets (represented by circles). The result-set (or newly joined table) obtained using full join is nothing but the union of these two sets.

fulljoin 1

Syntax

以下是 SQL 中满连接的基本语法：

Following is the basic syntax of Full Join in SQL −

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

Example

假设我们创建了一个名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括其姓名、年龄、地址和工资等。使用以下查询：

Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.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 −

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

该表将被创建为：

The table will be created as −

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 的表，其中包含所做的订单和下单日期的详细信息。

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 −

OID

DATE

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

以下查询在 SQL Server 中连接 CUSTOMER 和 ORDERS 两个表：

Following query joins the two tables CUSTOMERS and ORDERS in SQL Server −

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

Output

结果表如下：

The resultant table is produced as follows −

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

Joining Multiple Tables with Full Join

Full Join 查询也可用于连接不只是两个表。要做到这一点，我们需要一次顺序组合两个表，直到所有表连接在一起。

The Full Join query can also be used to join more than just two tables. To do that, we sequentially combine two tables at a time, until all the tables are joined together.

请注意，在 MySQL 数据库中，没有直接使用 FULL JOIN 关键字来对多张表执行联接操作的条款。相反，在两张表上对 LEFT JOIN 和 RIGHT JOIN 计算 UNION，直到所有表连接在一起。

Note that in MySQL database, there is no provision to directly use the FULL JOIN keyword to perform join operation on multiple tables. Instead, calculate the UNION of LEFT JOIN and RIGHT JOIN on two tables at a time, until all the tables are joined.

Syntax

使用 Full Join 连接多张表的语法如下：

The syntax to join multiple tables using Full Join is given below −

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

Example

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

To demonstrate Full Join, let us consider the sample tables CUSTOMERS and ORDERS that we previously created, and create another table name EMPLOYEE using the following query −

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

现在，我们可以使用 INSERT 语句将值插入到此空表中，如下所示：

Now, we can insert values into this empty tables using the INSERT statement as follows −

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

创建的 EMPLOYEE 表将如下图所示：

The EMPLOYEE table created, will be as shown below −

EID

EMPLOYEE_NAMENAME

SALES_MADE

102

SARIKA

4500

100

ALEKHYA

3623

101

REVATHI

1291

103

VIVEK

3426

让我们使用下面给出的 full join 查询连接这三张表：

Let us join these three tables using the full join query given below −

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、名称以及创建订单的日期和销售该商品的员工的名称。

Through this query, we will display the id, name of the customer along with the date on which the orders are made and the name of the employee who sold the item.

Output

结果表如下：

The resultant table is obtained as follows −

ID

NAME

DATE

EMPLOYEE_NAME

1

Ramesh

NULL

NULL

2

Khilan

2009-11-20 00:00:00

REVATHI

3

Kaushik

2009-10-08 00:00:00

ALEKHYA

3

Kaushik

2009-10-08 00:00:00

SARIKA

4

Chaitali

2008-05-20 00:00:00

VIVEK

5

Hardik

NULL

NULL

6

Komal

NULL

NULL

7

Muffy

NULL

NULL

Full Join with WHERE Clause

连接操作默认使用 ON 子句来筛选记录。我们假设需要根据某个条件/限制来进一步筛选这些记录，我们还可以将 WHERE 子句与连接操作一起使用。

Joins use the ON clause to filter records by default. Let us suppose there is a further requirement to filter these records based on a certain condition/constraint, we can also make use of the WHERE clause with Joins.

Syntax

当与 WHERE 子句一起使用时，Full Join 的语法如下：

The syntax of Full Join when used with WHERE clause is given below −

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

Example

考虑以前的两个表 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
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

Output

在使用 full join 应用 WHERE 子句后的结果表包含数量值大于 2000.00 的行：

The resultant table after applying the WHERE clause with full join contains the rows that has amount values greater than 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