多表聯查是關系型數據庫的核心操作，用于從多個表中關聯數據。MySQL 支持多種連接方式，最常用的是內連接和外連接（左/右/全外連接）。

---

一、多表聯查基礎語法

SELECT 列列表
FROM 表1
[連接類型] JOIN 表2 ON 連接條件
[連接類型] JOIN 表3 ON 連接條件
...
WHERE 篩選條件;

---

二、連接類型詳解

1. 內連接 (INNER JOIN)

特點：只返回兩個表中匹配成功的記錄
應用場景：需要獲取有關聯關系的完整數據

-- 基礎語法
SELECT e.name AS 員工姓名,d.name AS 部門名稱
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;-- 三表內連接示例
SELECTo.order_id,c.name AS 客戶名稱,p.product_name AS 產品名稱
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

2. 左外連接 (LEFT JOIN)

特點：返回左表所有記錄 + 右表匹配記錄（無匹配則顯示 NULL）
應用場景：包含主表全部記錄，關聯表可選信息

-- 獲取所有員工及其部門（含無部門員工）
SELECTe.name AS 員工,d.name AS 部門
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;-- 查找從未下單的客戶
SELECTc.name AS 客戶名
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;  -- 關鍵：通過NULL檢測未匹配項

3. 右外連接 (RIGHT JOIN)

特點：返回右表所有記錄 + 左表匹配記錄（無匹配則顯示 NULL）
應用場景：包含從表全部記錄，主表可選信息（較少使用，可用LEFT JOIN替代）

-- 獲取所有部門及員工（含無員工部門）
SELECTd.name AS 部門,e.name AS 員工
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;-- 等價LEFT JOIN寫法
SELECTd.name AS 部門,e.name AS 員工
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id;

4. 全外連接 (FULL OUTER JOIN)

特點：返回左右表所有記錄（無匹配則對側顯示 NULL）
注意：MySQL 不直接支持，需用 UNION 實現

-- 獲取所有員工和部門組合（含無部門員工+無員工部門）
SELECT e.name AS 員工,d.name AS 部門
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.idUNION  -- 使用UNION合并結果集SELECT e.name AS 員工,d.name AS 部門
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id
WHERE e.dept_id IS NULL;  -- 排除重復匹配項

5. 交叉連接 (CROSS JOIN)

特點：返回笛卡爾積（所有可能組合）
應用場景：生成組合數據（如測試數據）

-- 生成顏色和尺寸的所有組合
SELECT colors.color_name,sizes.size_name
FROM colors
CROSS JOIN sizes;

---

三、特殊連接場景

1. 自連接 (Self Join)

應用場景：表內數據關聯（如層級關系）

-- 查詢員工及其經理
SELECTemp.name AS 員工,mgr.name AS 經理
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.id;

2. 復合條件連接

-- 多條件連接（部門+地點）
SELECTe.name,d.name AS 部門,loc.city AS 城市
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id AND d.location_id = loc.id  -- 連接時添加額外條件
INNER JOIN locations loc ON d.location_id = loc.id;

3. USING 關鍵字

適用：當連接列名相同時簡化語法

-- 傳統寫法
SELECT *
FROM orders o
INNER JOIN order_items i ON o.id = i.order_id;-- 使用USING簡化
SELECT *
FROM orders
INNER JOIN order_items USING (id);  -- 要求兩表都有id列

---

四、性能優化與最佳實踐

1. 索引策略

   -- 為連接字段創建索引
   CREATE INDEX idx_dept ON employees(dept_id);
   CREATE INDEX idx_order ON order_items(order_id);
   

2. **避免 SELECT ***
   只選擇必要字段減少數據傳輸量

3. 連接順序優化

   • 小表驅動大表（小表在前）
   • 過濾條件多的表優先連接

4. 使用 EXPLAIN 分析

   EXPLAIN SELECT ...
   

   查看執行計劃，優化連接順序和索引使用

5. 替代方案考慮

   • 復雜連接可拆分為多個查詢
   • 大表連接考慮使用臨時表

---

五、綜合應用示例

-- 查詢2023年每個客戶的總消費金額（含未消費客戶）
SELECTc.id AS 客戶ID,c.name AS 客戶姓名,COALESCE(SUM(o.amount), 0) AS 總消費金額
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_idAND YEAR(o.order_date) = 2023  -- 連接時過濾
GROUP BY c.id
ORDER BY 總消費金額 DESC;-- 結果示例：
| 客戶ID | 客戶姓名 | 總消費金額 |
|---------|----------|------------|
| 101     | 張三     | 8500.00    |
| 105     | 李四     | 0.00       | 
| 102     | 王五     | 4200.00    |

---

六、常見錯誤及解決

1. 笛卡爾積問題
   現象：結果集異常膨脹
   解決：確保所有表都有連接條件

2. NULL 值匹配問題
   現象：預期外的記錄缺失
   解決：使用 IFNULL() 或 COALESCE() 處理

3. 性能低下
   現象：大表連接緩慢
   解決：

   -- 添加合適索引
   CREATE INDEX idx_name ON table(column);-- 分批處理
   SELECT ... LIMIT 1000 OFFSET 0;