联表查询在平时的项目中经常遇到，总觉得理解的不是很透彻，这里简单总结一下连表查询的使用。

比方我们有如下两张表：

• EmployeeTB：

employee_id	employee_name	dept_id
0001	张三	01
0002	李四	01
0003	王五	02
0004	赵六	02
0005	郑七	NULL

• DeptTB

dept_id	dept_name
01	技术部
02	市场部
03	工程部

从上面两张表可以看出他们存在dept id为关联的关系，所以就会存在如下需求：

1、找出EmployeeTB中员工与DeptTB中部门之间对应关系；

查询方式有多种，下面就做简单的概括：

• 内联结查询：

SELECT e.employee_id, e.employee_name, d.dept_name FROM EmployeeTB AS e, DeptTB AS d WHERE e.dept_id=d.dept_id;

SELECT e.employee_id, e.employee_name, d.dept_name FROM EmployeeTB AS e INNER JOIN DeptTB AS d ON e.dept_id=d.dept_id;

上面两句查询的效果是一样的：

mysql> SELECT e.employee_id, e.employee_name, d.dept_name FROM EmployeeTB AS e,
DeptTB AS d WHERE e.dept_id=d.dept_id;
+-------------+---------------+------------+
| employee_id | employee_name | dept_name  |
+-------------+---------------+------------+
|           1 | zhangsan      | jishubu    |
|           2 | lisi          | jishubu    |
|           3 | wangwu        | shichangbu |
|           4 | zhaoliu       | shichangbu |
+-------------+---------------+------------+
4 rows in set (0.00 sec)

• 外联结查询：

  • 1、左外联结：

如上面例子，有时候我们需要知道所有员工的信息，如果不属于任何部门，则dept_name字段用NULL补充。此时，我们就可以采用左外联结达到该效果：

SELECT e.employee_id, e.employee_name, d.dept_name FROM EmployeeTB AS e LEFT OUTER JOIN DeptTB AS d ON d.dept_id=e.dept_id;

查询结果：

mysql> SELECT e.employee_id, e.employee_name, d.dept_name FROM EmployeeTB AS e L
EFT OUTER JOIN DeptTB AS d ON d.dept_id=e.dept_id;
+-------------+---------------+------------+
| employee_id | employee_name | dept_name  |
+-------------+---------------+------------+
|           1 | zhangsan      | jishubu    |
|           2 | lisi          | jishubu    |
|           3 | wangwu        | shichangbu |
|           4 | zhaoliu       | shichangbu |
|           5 | zhengqi       | NULL       |
+-------------+---------------+------------+
5 rows in set (0.00 sec)

• 2、右外联结：

右外联结查询和左外联结查询中把两张表的位置互换是一致的。读者可以试试如下两个查询语句的执行结果：

SELECT d.employee_id, d.employee_name, e.dept_name FROM DeptTB AS e LEFT OUTER JOIN EmployeeTB AS d ON d.dept_id=e.dept_id;

SELECT e.employee_id, e.employee_name, d.dept_name FROM EmployeeTB AS e RIGHT OUTER JOIN DeptTB AS d ON d.dept_id=e.dept_id;

查询结果应该是如下的：

+-------------+---------------+-------------+
| employee_id | employee_name | dept_name   |
+-------------+---------------+-------------+
|           1 | zhangsan      | jishubu     |
|           2 | lisi          | jishubu     |
|           3 | wangwu        | shichangbu  |
|           4 | zhaoliu       | shichangbu  |
|        NULL | NULL          | gongchengbu |
+-------------+---------------+-------------+
5 rows in set (0.00 sec)

• 3、完全外联结：

如果我们需要知道所有的记录，不管部门下有没有员工，员工有没有所属的部门，我们都需要检索。那我们就需要用到完全外联结查询了。

SELECT e.employee_id, e.employee_name, d.dept_name FROM EmployeeTB AS e FULL OUTER JOIN DeptTB AS d ON d.dept_id=e.dept_id;

由于MYSQL目前不再支持完全外联结查询，可以使用如下方法实现相同的效果：

SELECT e.employee_id, e.employee_name, d.dept_name FROM EmployeeTB AS e LEFT OUTER JOIN DeptTB AS d ON d.dept_id=e.dept_id UNION SELECT e.employee_id, e.employee_name, d.dept_name FROM EmployeeTB AS e RIGHT OUTER JOIN DeptTB AS d ON d.dept_id=e.dept_id;

mysql> SELECT e.employee_id, e.employee_name, d.dept_name FROM EmployeeTB AS e L
EFT OUTER JOIN DeptTB AS d ON d.dept_id=e.dept_id UNION SELECT e.employee_id, e.
employee_name, d.dept_name FROM EmployeeTB AS e RIGHT OUTER JOIN DeptTB AS d ON
d.dept_id=e.dept_id;
+-------------+---------------+-------------+
| employee_id | employee_name | dept_name   |
+-------------+---------------+-------------+
|           1 | zhangsan      | jishubu     |
|           2 | lisi          | jishubu     |
|           3 | wangwu        | shichangbu  |
|           4 | zhaoliu       | shichangbu  |
|           5 | zhengqi       | NULL        |
|        NULL | NULL          | gongchengbu |
+-------------+---------------+-------------+
6 rows in set (0.01 sec)

---

参考资料：

• 数据库多表连接查询