MySQL-06.多表查詢_ZenDei技術網路在線

C-06.多表查詢多表查詢，也稱為關聯查詢，指兩個或更多個表一起完成查詢操作。前提條件，這些一起查詢的表之間是有關係的(一對一，一對多等)，它們之間一定是有關聯欄位，這個關聯欄位可能建立了外鍵，也可能沒有建立外鍵。 1.一個案例引發的多表連接 1.1 案例說明涉及到的表結構查詢員工名(las ...

C-06.多表查詢

多表查詢，也稱為關聯查詢，指兩個或更多個表一起完成查詢操作。
前提條件，這些一起查詢的表之間是有關係的(一對一，一對多等)，它們之間一定是有關聯欄位，這個關聯欄位可能建立了外鍵，也可能沒有建立外鍵。

1.一個案例引發的多表連接

1.1 案例說明

涉及到的表結構

查詢員工名(last_name)為'Abel'在那個城市工作。

1.1.2 非多表查詢實現

-- 第一步，首先查出'Abel'所在的department_id 部門id
SELECT department_id FROM employees WHERE last_name = 'Abel' LIMIT 0,1;
-- 第二步，查出Abel所在部門，對應的location_id 地區id
SELECT location_id FROM departments WHERE department_id = ();//第一步查出的數據
-- 第三步，根據location_id獲取，city信息
SELECT city FROM locations WHERE location_id = ();//第二步查出的數據

但是，這樣有問題，為了查出Abel工作的城市，需要執行3次sql，很浪費時間。

1.1.3 多表查詢實現

-- 使用多表查詢中
SELECT last_name,city
FROM employees,departments,locations
WHERE employees.department_id = departments.department_id 
	AND departments.location_id = locations.location_id;
-- 上述可以優化
SELECT e.last_name,l.city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id 
	AND d.location_id = l.location_id;

為什麼，多表查詢，以及優化的內容要這樣寫，後續會介紹。

1.2 笛卡爾積(交叉連接)的理解

笛卡爾積是一個數學運算。假設有兩個集合X{a,b}，Y{m,n}，那麼X和Y的笛卡爾積就是X和Y的所有可能組合，也就是這樣一個公式
X={a,b}，Y={m,n}，X和Y的笛卡爾積 = {a,m},{a,n},{b,m},{b,n};
對應的兩張表裡的笛卡爾積就是，假設a表和b表，a表和b表的笛卡爾積就是a表中的任意一條數據和b表中的任意一條數據組合。理想情況下，a表有m條數據，b表有n條數據，則下a表和b表的笛卡爾積(也就是進行聯合查詢)後的表，應該有m * n條數據。
這裡用employees表和departments表進行笛卡爾積演示。

mysql> SELECT COUNT(*) FROM employees;//count(*)統計表中非空列的數據
+----------+
| COUNT(*) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM departments;
+----------+
| COUNT(*) |
+----------+
|       27 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM employees,departments;
+----------+
| COUNT(*) |
+----------+
|     2889 |
+----------+
1 row in set (0.00 sec)//107 * 27 = 2889

在SQL92(1992年發佈的SQL標準)中，笛卡爾積也稱為交叉連接，英文是CROSS JOIN。在SQL99(1999年發佈的SQL標準)也是使用CROSS JOIN表示交叉連接。它的作用就是可以把任意表進行連接，即使這兩張表不相關。在MySQL中如下情況下會出現笛卡爾積：

-- 以employees，departments表舉例
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;-- INNER JOIN 內連接
SELECT last_name,department_name FROM employees JOIN departments;

1.3 案例分析和問題解決

會出現笛卡爾積的原因
- 省略多個表的連接條件(也叫過濾笛卡爾積的條件)。
- 連接條件無效。
- 所有表中的所有行互相連接(很少出現)。
為了避免笛卡爾積，可以在WHERE加入有效的連接條件(但是連接條件，並不是只能寫在WHERE子句中，後續的外連接是寫在ON子句)。
加入連接條件後，查詢語法：

SELECT t1.column_name,t2.column_name
FROM table1 t1,table2 t2
WHERE t1.column1 = t2.column2; -- 連接條件，應用到實際的表，需要替換實際的列名

使用employees，deparments表寫案例:

SELECT e.last_name,d.department_name
FROM employees e,departments d
WHERE e.dapartment_id = d.dapartment_id;
-- 在進行多表查詢時，可以給表起別名，但是要註意，起表的別名，和列名的別名不同。可以省略as關鍵字和列別名相同，但是表名不能寫在''中，否則會報錯。

mysql> SELECT e.last_name,d.department_name
    -> FROM employees 'e',department 'd'
    -> WHERE e.department_id = d.department_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''e',department 'd'
WHERE e.department_id = d.department_id' at line 2

在不同的表中有相同列時，必須在列名前要加上使用的表名首碼。

2.多表查詢分類講解

2.1 等值連接 vs 非等值連接

2.1.1 等值連接

mysql> SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id
    -> FROM employees e,departments d
    -> WHERE e.department_id = d.department_id;
+-------------+-------------+---------------+---------------+-------------+
| employee_id | last_name   | department_id | department_id | location_id |
+-------------+-------------+---------------+---------------+-------------+
|         103 | Hunold      |            60 |            60 |        1400 |
|         104 | Ernst       |            60 |            60 |        1400 |
|         105 | Austin      |            60 |            60 |        1400 |
|         106 | Pataballa   |            60 |            60 |        1400 | -- 只顯示一部分數據，一共有106行數據

拓展1：多個連接條件要使用AND操作符。
拓展2：區分重覆的別名：

進行表查詢的表中有相同列時，必須在列名之前加上表名首碼進行區分，使用的是那張表的列數據。

拓展3：表的別名。

使用別名可以簡化查詢。
列名前使用表名首碼可以提高查詢效率。
但是需要註意，如果給表起了別名，在查詢欄位中，過濾條件中就只能使用別名進行代替，不能使用原有的表名，否則就會報錯。
建議，從SQL優化的角度，建議多表查詢時，每個欄位前都指明其所在的表。

拓展4：連接多個表
總結：連接n個表，至少需要n-1個連接條件。比如，連接三個表，至少需要兩個連接條件。

2.2.2 非等值連接

-- 查詢員工薪資和薪資等級
SELECT e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary >= lowest_sal AND e.salary <= highest.sal;

2.2 自連接 vs 非自連接

當table1和table2是同一張表，只是採用起別名的方式虛擬成兩張表，代表不同的意義。然後兩張表進行連接查詢，就是自連接。
employees表中，有employee_id列，和manager_id列。

-- 查詢員工和其對應領導的信息
SELECT     
	w.`employee_id` AS 'worker_id',
    w.`last_name` AS 'worker_name',
    m.`employee_id` AS 'manager_id',
    m.`last_name` AS 'manager_name'
FROM 
	employees w,employees m
WHERE 
	w.manager_id = m.employee_id;
-- 但是這裡會有個小漏洞，就是可能會遺漏manager_id是null的數據

mysql> SELECT
    -> w.`employee_id` AS 'worker_id',
    ->     w.`last_name` AS 'worker_name',
    ->     m.`employee_id` AS 'manager_id',
    ->     m.`last_name` AS 'manager_name'
    -> FROM
    -> employees w,employees m
    -> WHERE
    -> w.manager_id = m.employee_id;
+-----------+-------------+------------+--------------+
| worker_id | worker_name | manager_id | manager_name |
+-----------+-------------+------------+--------------+
|       101 | Kochhar     |        100 | King         |
|       102 | De Haan     |        100 | King         |
|       103 | Hunold      |        102 | De Haan      |
...
|       204 | Baer        |        101 | Kochhar      |
|       205 | Higgins     |        101 | Kochhar      |
|       206 | Gietz       |        205 | Higgins      |
+-----------+-------------+------------+--------------+
106 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)

從執行結果看，此自連接語句，查詢一共有106條數據，但是employees表有107條數據。這是因為內連接(從另一個分類角度，該條自連接語句也屬於內連接)，對於連接條件為NULL的一行數據會不顯示。

mysql> SELECT
    ->     w.`employee_id` AS 'worker_id',
    ->     w.`last_name` AS 'worker_name',
    ->     m.`employee_id` AS 'manager_id',
    ->     m.`last_name` AS 'manager_name'
    -> FROM
    ->     employees w
    ->     LEFT JOIN employees m
    ->         ON w.manager_id = m.employee_id;
+-----------+-------------+------------+--------------+
| worker_id | worker_name | manager_id | manager_name |
+-----------+-------------+------------+--------------+
|       100 | King        |       NULL | NULL         |
|       101 | Kochhar     |        100 | King         |
|       102 | De Haan     |        100 | King         |
...
|       203 | Mavris      |        101 | Kochhar      |
|       204 | Baer        |        101 | Kochhar      |
|       205 | Higgins     |        101 | Kochhar      |
|       206 | Gietz       |        205 | Higgins      |
+-----------+-------------+------------+--------------+
107 rows in set (0.00 sec)

這裡使用外連接，就會顯示107條數據其中worker_id = 107的一行數據，就是上述自連接不顯示的一條。下麵進行內連接和外連接的講解。

2.3 內連接 vs 外連接

除了查詢滿足條件的記錄以外，外連接還可以查詢某一方不滿足條件的記錄。

內連接，合併具有同一列的兩個以上的表的行，結果集中不包含一個表與另一個表不匹配的行
外連接，兩個表在連接過程中除了返回滿足連接條件的行以外，還返回左(或右)表中不滿足條件的行，這種連接稱為左(或右)外連接。沒有匹配的行時，結果表中相應的列為null
如果是左外連接，則連接條件中左邊的表為主表,右邊的表為從表。
如果是右外連接，則連接條件中右邊的表為主表，左邊的表為從表。

SQL 92 使用(+)創建連接

在SQL 92中採用(+)代表從表所在的位置。即左或右外連接中，(+)在連接條件的那邊那個就是從表。
Oracle對SQL92支持較好，而MySQL則不支持SQL92的外連接
在SQL 92中，只有左外連接和右外連接，沒有滿(或全)外連接。

3.SQL99語法實現多表查詢

3.1 基本語法

使用JOIN...ON子句創建連接的語法結構：

SELECT table1.column,table2.column,table3.column
FROM table1 
	JOIN table2 ON -- table1和table2的連接條件
		JOIN table3 ON -- table2和table3的連接條件

它的嵌套邏輯類似我們使用的FOR迴圈：

for t1 in table1:
	for t2 in table2:
		if condition1:
			for t3 in table3:
				if condition2:
					output t1 + t2 + t3;

SQL99採用的這種嵌套結構，非常清爽，層次性更強，可讀性更強，即是更多的表進行連接也都清晰可見。如果採用SQL92，可讀性就會大打折扣。

語法說明
- 可使用ON子句指定額外的連接條件。
- 這個連接條件是與其他條件分開的。
- ON子句使語句具有更高的可讀性。
- 關鍵字JOIN，INNER JOIN，CROSS JOIN的含義是一樣的，都表示為內連接。

3.2 內連接(INNER JOIN)的實現

語法：

SELECT 欄位列表
FROM A表 INNER JOIN B表
ON 關聯條件
WHERE 過濾條件
-- INNER關鍵字可省略

mysql> SELECT e.employee_id,d.department_name,d.department_id
    -> FROM employees AS e INNER JOIN departments AS d
    -> ON e.department_id = d.department_id;
+-------------+------------------+---------------+
| employee_id | department_name  | department_id |
+-------------+------------------+---------------+
|         200 | Administration   |            10 |
|         201 | Marketing        |            20 |
|         202 | Marketing        |            20 |
...
|         113 | Finance          |           100 |
|         205 | Accounting       |           110 |
|         206 | Accounting       |           110 |
+-------------+------------------+---------------+
106 rows in set (0.01 sec)


mysql> SELECT
    ->     e.employee_id,
    ->     d.department_name,
    ->     d.department_id,
    ->     l.city
    -> FROM
    ->     employees e
    ->     JOIN departments d
    ->         ON e.department_id = d.department_id
    ->     JOIN locations l
    ->         ON d.`location_id` = l.location_id;
+-------------+------------------+---------------+---------------------+
| employee_id | department_name  | department_id | city                |
+-------------+------------------+---------------+---------------------+
|         200 | Administration   |            10 | Seattle             |
|         201 | Marketing        |            20 | Toronto             |
|         202 | Marketing        |            20 | Toronto             |
...
|         113 | Finance          |           100 | Seattle             |
|         205 | Accounting       |           110 | Seattle             |
|         206 | Accounting       |           110 | Seattle             |
+-------------+------------------+---------------+---------------------+
106 rows in set (0.00 sec)

3.3 外連接(OUTER JOIN)的實現

3.3.1 左外連接(LEFT OUTER JOIN)

語法

-- 主表是A OUTER關鍵字可以省略
SELECT 欄位列表
FROM A表 LEFT OUTER JOIN B表
ON 關聯條件
WHERE 過濾條件

舉例

mysql> SELECT e.employee_id,d.department_name,d.department_id
    -> FROM employees e LEFT OUTER JOIN departments d
    -> ON e.department_id = d.department_id;
+-------------+------------------+---------------+
| employee_id | department_name  | department_id |
+-------------+------------------+---------------+
|         178 | NULL             |          NULL |
|         200 | Administration   |            10 |
|         201 | Marketing        |            20 |
|         202 | Marketing        |            20 |
...
|         112 | Finance          |           100 |
|         113 | Finance          |           100 |
|         205 | Accounting       |           110 |
|         206 | Accounting       |           110 |
+-------------+------------------+---------------+
107 rows in set (0.00 sec)

3.3.2 右外連接(RIGHT OUTER JOIN)

語法

-- 主表是B表 OUTER關鍵詞也可以省略
SELECT 欄位列表
FROM A表 RIGHT OUTER JOIN B表
ON 關聯條件
WHERE 過濾條件

舉例

mysql> SELECT e.employee_id,d.department_name,d.department_id
    -> FROM employees e RIGHT JOIN departments d
    -> ON e.department_id = d.department_id;
+-------------+----------------------+---------------+
| employee_id | department_name      | department_id |
+-------------+----------------------+---------------+
|         200 | Administration       |            10 |
|         201 | Marketing            |            20 |
|         202 | Marketing            |            20 |
|         114 | Purchasing           |            30 |
...
|        NULL | Government Sales     |           240 |
|        NULL | Retail Sales         |           250 |
|        NULL | Recruiting           |           260 |
|        NULL | Payroll              |           270 |
+-------------+----------------------+---------------+
122 rows in set (0.00 sec)

註意，LEFT JOIN和RIGHT JOIN只存在於SQL99及以後的標準中，在SQL92中不存在，只能用(+)表示。

3.3.3 滿外連接(FULL OUTER JOIN)

滿外連接的結果 = 左右表匹配的數據 + 左表沒有匹配到的數據 + 右表沒有匹配到的數據。
SQL99是支持滿外連接的。使用FULL JOIN或FULL OUTER JOIN來實現。
需要註意的是，MySQL不支持FULL JOIN，但是可以用LEFT JOIN UNION RIGHT JOIN等替代。

4.UNION的使用

合併查詢結果利用UNION關鍵字，可以給出多條SELECT語句，並將它們的結果組合成單個結果集。合併時，兩個表對於的列數和數據類型必須相同，並且互相對應。各個SELECT語句之間使用UNION或UNION ALL關鍵字分隔。
語法格式：

SELECT column,... FROM t1
UNION [ALL]
SELECT column,... FROM t2

UNION操作符

UNION 操作符返回兩個查詢的結果集的並集，去除重覆記錄。

UNION ALL操作符

UNION ALL操作符返回兩個查詢的結果集的並集。對於兩個結果集的重覆部分，不去重。
註意，執行UNION ALL 語句時所需要的資源比UNION語句少，如果明確知道合併數據後的結果數據不存在重覆數據，或者不需要去除重覆的數據，則儘量使用UNION ALL語句，以提高數據查詢的效率。
5. 7種SQL JOINS的實現

5.7.1 SQL實現

# 7種JOIN 使用employees表和departments表代表A和B 
#中圖
SELECT e.employee_id,d.department_name,d.department_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id;


# 左上圖
# 分析，將e表和d表比作兩個集合的話
# 左上圖就是 (e - e ∩ d) ∪ ( e ∩ d) 在MySQL中用LEFT JOIN ... ON實現
# 分析，為什麼LEFT JOIN ... ON可以實現
# 因為，在外連接中，相當於有一個表是主表，另一個表是從表，在外連接中
# 主表的數據一定要全部實現，對於主表中有數據，而從表沒有與之對於連接條件成立的數據
# 就將要顯示的從表中的列補null進行顯示
SELECT e.employee_id,d.department_name,d.department_id
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;


# 右上圖
# 分析，將e表和d表比作兩個集合的話
# 左上圖就是 (d - e ∩ d) ∪ ( e ∩ d) 在MySQL中用RIGHT JOIN ... ON實現
SELECT e.employee_id,d.department_name,d.department_id
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

#分析主從表就是，左連接左是主表，右連接右是主表
# 左中圖
# 分析，將e表和d表比作兩個集合的話
# 左中圖就是 (e - e ∩ d) 
# 就是在左上圖(e和d表進行左連接)的前提下，去掉e表和d表中都出現的數據
# 只保留主表中有數據，但是從表無數據的列 
# 從表無數據就是，從表的過濾條件中不滿足，所以過濾條件是 WHERE d.department_id IS NULL
SELECT e.employee_id,d.department_name,d.department_id
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

# 右中圖
# 分析，將e表和d表比作兩個集合的話
# 右中圖就是 (d - e ∩ d) 
# 就是在右上圖(e和d表進行右連接)的前提下，去掉e表和d表中都出現的數據
# 只保留主表中有數據，但是從表無數據的列 
# 從表無數據就是，從表的過濾條件中不滿足，所以過濾條件是 WHERE e.department_id IS NULL
SELECT e.employee_id,d.department_name,d.department_id
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

# 左下圖
# 就是滿外連接
# MySQL實現滿外連接
# 使用到UNION/UNION ALL關鍵字
# 可以用左上圖 UNION 右上圖
# 也可以用左上圖 UNION ALL 右中圖
# 也可以用左中圖 UNION ALL 右上圖
-- 具體的SQL語句略

# 右下圖
# 就是左中圖 UNION ALL 右中圖
SELECT e.employee_id,d.department_name,d.department_id
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT e.employee_id,d.department_name,d.department_id
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

5.7.2 語法格式小結

左中圖

-- 實現 A - A ∩ B
SELECT 欄位列表
FROM A LEFT JOIN B
ON 關聯條件
WHERE 從表關聯欄位 IS NULL ANd 其他過濾子句;

右中圖

-- 實現B - A ∩ B
SELECT 欄位列表
FROM A RIGHT JOIN B
ON 關聯條件
WHERE 從表關聯欄位 IS NULL ANd 其他過濾子句;

6.SQL99語法新特性

6.1 自然連接

SQL99在SQL92的基礎上，提供了一些特殊語法，比如 NATURAL JOIN用來表示自然連接。我們可以把自然連接理解為SQL92中的等值連接。它會自動查詢兩張表裡所有相同的欄位，然後進行等值連接。

-- 等值連接
SELECT e.employee_id,d.department_name,d.department_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
AND e.`manager_id` = d.`manager_id`;

-- 使用NATURAL JOIN新特性實現等值連接
SELECT e.employee_id,d.department_name,d.department_id
FROM employees e NATURAL JOIN departments d;

6.2 USING連接

SELECT e.employee_id,d.department_name,d.department_id
FROM employees e JOIN departments d
USING (department_id);
-- 等價於
SELECT e.employee_id,d.department_name,d.department_id
FROM employees e JOIN departments d
USING e.department_id = d.department_id;

7.章節小結

表連接的約束條件可以有三種方式，WHERE，ON，USING

WHERE：適用於所有關聯查詢。
ON：只能和JOIN一起使用，只能寫關聯條件。雖然關聯條件可以併到WHERE中和其他條件一起寫，但分開寫可讀性更好。
USING: 只能和JOIN一起使用，而且要求關聯欄位在兩張表中相同，而且只能表示關聯欄位相等。
註意：
要控制連接表的數量，多表連接就相當於嵌套for迴圈一樣，非常消耗資源，會讓SQL查詢性能下降很嚴重，因此不要連接不必要的表。在許多DBMS中，也都會有最大連接表限制。

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。