联表查询在平时的项目中经常遇到，总觉得理解的不是很透彻，这里简单总结一下连表查询的使用。
比方我们有如下两张表：
- 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)
网友评论