聯(lián)表查詢在平時的項目中經(jīng)常遇到，總覺得理解的不是很透徹，這里簡單總結(jié)一下連表查詢的使用。

比方我們有如下兩張表：

• EmployeeTB：

• DeptTB

從上面兩張表可以看出他們存在dept id為關(guān)聯(lián)的關(guān)系，所以就會存在如下需求：

1. 找出EmployeeTB中員工與DeptTB中部門之間對應關(guān)系；

查詢方式有多種，下面就做簡單的概括：

• 內(nèi)聯(lián)結(jié)查詢：

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)

• 外聯(lián)結(jié)查詢：

  • 1、左外聯(lián)結(jié)：

如上面例子，有時候我們需要知道所有員工的信息，如果不屬于任何部門，則dept_name字段用NULL補充。此時，我們就可以采用左外聯(lián)結(jié)達到該效果：

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;

查詢結(jié)果：

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、右外聯(lián)結(jié)：

右外聯(lián)結(jié)查詢和左外聯(lián)結(jié)查詢中把兩張表的位置互換是一致的。讀者可以試試如下兩個查詢語句的執(zhí)行結(jié)果：

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;

查詢結(jié)果應該是如下的：

+-------------+---------------+-------------+
| 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、完全外聯(lián)結(jié)：

如果我們需要知道所有的記錄，不管部門下有沒有員工，員工有沒有所屬的部門，我們都需要檢索。那我們就需要用到完全外聯(lián)結(jié)查詢了。

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目前不再支持完全外聯(lián)結(jié)查詢，可以使用如下方法實現(xiàn)相同的效果：

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)

參考資料：

• 數(shù)據(jù)庫多表連接查詢