全连接返回连接表中的所有行，不管它们是否匹配，也就是说，全连接组合了左连接和右连接的功能。全连接是一种外部连接，也被称为完全外部连接。
下图说明了全连接工作原理。
为了更清楚地理解，让我们看看下面的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，表示数据不存在。