---

精選專欄鏈接 🔗

---

• MySQL技術筆記專欄
• Redis技術筆記專欄
• 大模型搭建專欄
• Python學習筆記專欄
• 深度學習算法專欄

歡迎訂閱，點贊＋關注，每日精進1%，與百萬開發者共攀技術珠峰

更多內容持續更新中！希望能給大家帶來幫助~ 😀😀😀

---

---

1，為什么需要多表查詢

多表查詢，也稱為關聯查詢，指兩個或更多個表一起完成查詢操作。

可進行多表查詢的前提條件： 這些一起查詢的表之間是有關系的（一對一、一對多），它們之間一定是有關聯字段，比如：員工表和部門表，這兩個表依靠“部門編號”進行關聯。

案例說明為什么需要多表查詢：

如下圖所示的是一個項目的三張表：EMPLOYEES表（員工表）、DEPARTMENTS表、LOCATIONS表。

顯然，我們可以得到如下信息：

• EMPLOYEES 表和DEPARTMENTS表通過 department_id 字段相關聯；
• DEPARTMENT表和 LOCATIONS 表通過 location_id 字段相關聯；

如果我們現在有一個新需求：要求查詢員工名為 “Abel” 的人在哪個城市工作？

顯然 EMPLOYEES 表中沒有城市這個字段，城市字段位于 LOCATIONS 表內。
我們可以通過如下步驟完成此需求。

第一步：在EMPLOYEES表內查詢Abel的員工信息

SQL語句如下：

SELECT * 
FROM employees
WHERE last_name = 'Abel';

運行結果如下：

可以看到Abel 所在的 department_id 為80。

第二步：在DEPARTMENT表內查詢department_id 為80的部門信息

SQL語句如下：

SELECT *
FROM departments
WHERE department_id = 80;

運行結果如下：

可以看到department_id 為80的部門對應的location_id是2500。

第二步：在LOCATIONS表內查詢location_id是2500的地址信息

SQL語句如下：

SELECT *
FROM locations 
WHERE location_id = 2500;

運行結果如下：

由此可見，員工Abel的工作城市是Oxford。

---

寫了三條SQL語句才實現此需求，找到了Abel的工作城市，這樣是很不方便的，而且在高并發的系統中，執行多個SQL語句對效率和性能的影響是比較大的（相當于多次交互）。因此要引入多表查詢，通過多表查詢可以實現 一條SQL語句完成此需求。

---

2，什么是笛卡爾積

笛卡爾乘積是一個數學運算。假設我有兩個集合 X 和 Y，那么 X 和 Y 的笛卡爾積就是 X 和 Y 的所有可能組合，也就是第一個對象來自于 X，第二個對象來自于 Y 的所有可能。組合的個數即為兩個集合中元素個數的乘積數。如下圖所示：

---

3，多表查詢的笛卡爾積錯誤

多表查詢的一個常見錯誤就是笛卡爾積錯誤。

---

3.1，笛卡爾積錯誤案例

當我們有如下需求時：

需求：查詢每一位員工的employee_id和department_name。

注意： 如下圖所示，員工的employee_id位于EMPLOYEES表，而department_name字段位于DEPARTMENTS表。

如果執行如下SQL語句，得到的是錯誤結果：

SELECT employee_id,department_name
FROM employees,departments;  

運行結果如下：

一共查詢出2889條記錄，而EMPLOYEES表有107條記錄；DEPARTMENTS表有27條記錄。27?107＝2889，它把每個員工都與每個部門匹配了一遍，顯然這是一種錯誤的實現方式，具體來說是出現了笛卡爾積的錯誤。

錯誤的原因是：缺少了多表的連接條件。

---

3.2，笛卡爾積錯誤的分析和解決

笛卡爾積錯誤會在下面條件下產生：

• 省略多個表的連接條件（或關聯條件）；
• 連接條件（或關聯條件）失效；
• 所有表中的所有行互相連接；

---

正確的多表查詢需要有連接條件。為了避免笛卡爾積錯誤，可以通過WHERE子句加入有效的連接條件。

加入連接條件后的查詢語法如下：

SELECT table1.column, table2.column
FROM  table1, table2
WHERE table1.column1 = table2.column2;  #連接條件

因此，正確的SQL語句應該是：

SELECT employee_id,department_name
FROM employees,departments
# 兩個表的連接條件
WHERE employees.department_id = departments.department_id;

運行結果如下：

此時查詢得到的結果才是正常的。

注意：如上SQL查詢得到106條記錄，而EMPLOYEES表內有107條記錄，原因是EMPLOYEES表中存在一條記錄的 department_id 字段為Null。

---

3.3，公共字段的處理

一個細節問題是：如果查詢語句中出現了多個表中都存在的字段，則必須指明此字段所在的表。接下來結合案例解釋：

需求：查詢每一位員工的employee_id、department_name、department_id。

如果執行如下SQL：

SELECT employee_id,department_name,department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;

執行報錯：

原因是：EMPLOYEES表和DEPARTMENTS表都存在字段 departmen_id，SQL語句中沒有明確指出查詢哪個表中的 departmen_id 字段。

因此正確的SQL語句是：

# 如果查詢語句中出現了多個表中都存在的字段，則必須指明此字段所在的表。
SELECT employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;

運行結果如下：

我們了解到，如果查詢語句中出現了多個表中都存在的字段，則必須指明此字段所在的表。對此規則進行拓展：從sql優化的角度，建議多表查詢時，每個字段前都指明其所在的表。

因為如果不指明字段所在的表，MySQL會自己去兩張表中找此字段，找到后還需要檢查另外一張表中是否存在此字段。這會在一定程度上影響查詢的性能。因此建議多表查詢時，每個字段前都指明其所在的表。

即，更好的SQL語句如下：

SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;

練習

我們再提出一個新的需求用做練習。

需求：查詢每一位員工的employee_id、last_name、department_name、city。

SQL語句如下：

SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

因此可以總結出規律：如果有n個表實現多表的查詢，則需要至少n-1個連接條件。

---

歡迎訂閱，點贊＋關注，每日精進1%，與百萬開發者共攀技術珠峰

更多內容持續更新中！希望能給大家帶來幫助~ 😀😀😀