SQL FULL JOIN (全连接)

全连接返回连接表中的所有行,不管它们是否匹配,也就是说,全连接组合了左连接和右连接的功能。全连接是一种外部连接,也被称为完全外部连接。

下图说明了全连接工作原理。

SQL FULL JOIN

为了更清楚地理解,让我们看看下面的employees和departments表。

employees

+--------+--------------+------------+---------+
| emp_id | emp_name     | hire_date  | dept_id |
+--------+--------------+------------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
|      2 | Tony Montana | 2002-07-15 |       1 |
|      3 | Sarah Connor | 2005-10-18 |       5 |
|      4 | Rick Deckard | 2007-01-03 |       3 |
|      5 | Martin Blank | 2008-06-24 |    NULL |
+--------+--------------+------------+---------+


departments

+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+

现在,假设您想检索所有员工的名称和可用部门的名称,而不管它们在另一个表中是否有对应的行,可以使用全连接查询。

下面的语句使用公共字段dept_id将employees和departments表连接在一起,来检索所有部门以及所有员工的详细信息。

SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 FULL JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_name;

有些数据库,如Oracle、MySQL不支持全连接。可以使用UNION ALL语句来组合左连接和右连接,如下所示:

SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id
UNION ALL
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 RIGHT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_name;

执行上述命令后,将得到如下输出:

+--------+--------------+------------+------------------+
| emp_id | emp_name     | hire_date  | dept_name        |
+--------+--------------+------------+------------------+
|   NULL | NULL         | NULL       | Customer Service |
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources  |
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources  |
|      5 | Martin Blank | 2008-06-24 | NULL             |
|      4 | Rick Deckard | 2007-01-03 | Finance          |
|      4 | Rick Deckard | 2007-01-03 | Finance          |
|      3 | Sarah Connor | 2005-10-18 | Sales            |
|      3 | Sarah Connor | 2005-10-18 | Sales            |
|      2 | Tony Montana | 2002-07-15 | Administration   |
|      2 | Tony Montana | 2002-07-15 | Administration   |
+--------+--------------+------------+------------------+

结果集包括了department和employees表中的所有行。

出现在JOIN子句左边的表即是左表,右边的是右表。

当执行外连接时,当某个表中的行在另一个表中没有匹配行时,结果行中的对应字段会填空NULL,表示数据不存在。



浙ICP备17015664号 浙公网安备 33011002012336号 联系我们 网站地图  
@2019 qikegu.com 版权所有,禁止转载