一、JOIN 的作用與分類
JOIN 操作用于合并兩個或多個表的行，基于表之間的關聯字段。以下是常見的 JOIN 類型：
| JOIN 類型 | 描述 |
|---|---|
INNER JOIN | 返回兩個表匹配的記錄 |
LEFT JOIN | 返回左表所有記錄 + 右表匹配記錄（右表無匹配則為NULL） |
RIGHT JOIN | 返回右表所有記錄 + 左表匹配記錄（左表無匹配則為NULL） |
FULL JOIN | 返回所有記錄（MySQL不支持，可用UNION模擬） |
CROSS JOIN | 返回笛卡爾積（所有可能的組合） |
二、INNER JOIN（內連接）
語法與作用
SELECT 字段
FROM 表A
INNER JOIN 表B ON 表A.字段 = 表B.字段;
- 作用：僅返回兩表中匹配的行
- 使用場景：需要精確關聯數據的場景（如訂單與用戶信息關聯）
示例
表結構：
-- 用戶表
CREATE TABLE users (user_id INT PRIMARY KEY,name VARCHAR(50)
);
INSERT INTO users VALUES (1, '張三'), (2, '李四');-- 訂單表
CREATE TABLE orders (order_id INT PRIMARY KEY,user_id INT,amount DECIMAL(10,2)
);
INSERT INTO orders VALUES (1001, 1, 299.00), (1002, 3, 599.00);
查詢：獲取有訂單的用戶信息
SELECT u.name, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
結果：
| name | order_id | amount |
|---|---|---|
| 張三 | 1001 | 299.00 |
三、LEFT JOIN（左連接）
語法與作用
SELECT 字段
FROM 表A
LEFT JOIN 表B ON 表A.字段 = 表B.字段;
- 作用：返回左表所有記錄，右表無匹配則顯示NULL
- 使用場景：統計所有用戶的訂單情況（包括未下單用戶）
示例
查詢：統計所有用戶的訂單（含未下單用戶）
SELECT u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;
結果：
| name | order_id | amount |
|---|---|---|
| 張三 | 1001 | 299.00 |
| 李四 | NULL | NULL |
四、RIGHT JOIN（右連接）
語法與作用
SELECT 字段
FROM 表A
RIGHT JOIN 表B ON 表A.字段 = 表B.字段;
- 作用：返回右表所有記錄，左表無匹配則顯示NULL
- 使用場景：查找所有訂單對應的用戶（包括無效用戶訂單）
示例
查詢：顯示所有訂單及用戶信息
SELECT u.name, o.order_id, o.amount
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id;
結果：
| name | order_id | amount |
|---|---|---|
| 張三 | 1001 | 299.00 |
| NULL | 1002 | 599.00 |
五、FULL JOIN（全連接）
語法與作用（MySQL實現方式）
SELECT 字段
FROM 表A
LEFT JOIN 表B ON 表A.字段 = 表B.字段
UNION
SELECT 字段
FROM 表A
RIGHT JOIN 表B ON 表A.字段 = 表B.字段;
- 作用：返回所有記錄（類似LEFT JOIN + RIGHT JOIN去重）
- 使用場景：需要同時保留兩個表所有記錄的統計
示例
查詢：合并用戶和訂單的所有記錄
SELECT u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
UNION
SELECT u.name, o.order_id, o.amount
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id;
結果：
| name | order_id | amount |
|---|---|---|
| 張三 | 1001 | 299.00 |
| 李四 | NULL | NULL |
| NULL | 1002 | 599.00 |
六、復合條件 JOIN
多表關聯
-- 三表關聯示例
SELECT u.name,o.order_id,p.product_name
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
INNER JOIN products p ON o.product_id = p.product_id;
多條件關聯
-- 日期范圍關聯
SELECT e.emp_name,d.dept_name,s.salary
FROM employees e
LEFT JOIN salaries s ON e.emp_id = s.emp_id AND s.effective_date BETWEEN '2023-01-01' AND '2023-12-31';
七、自連接（Self Join）
語法與作用
SELECT A.字段, B.字段
FROM 表 AS A
JOIN 表 AS B ON A.關聯字段 = B.關聯字段;
- 使用場景：層級數據查詢（如員工與上級經理）
示例
表結構：
-- 創建員工表（包含員工ID、姓名和直屬上級ID）
CREATE TABLE employees (emp_id INT PRIMARY KEY, -- 員工ID（主鍵）name VARCHAR(50), -- 員工姓名manager_id INT -- 直屬上級的員工ID（引用emp_id）
);-- 插入示例數據（構建管理層級關系）
INSERT INTO employees VALUES
(1, 'CEO', NULL), -- CEO沒有上級（manager_id為NULL）
(2, 'CTO', 1), -- CTO的上級是CEO（emp_id=1）
(3, '工程師', 2); -- 工程師的上級是CTO（emp_id=2）
查詢：顯示員工及其上級
-- 查詢員工及其對應上級姓名（包含無上級的員工）
SELECT e.name AS employee, -- 員工姓名m.name AS manager -- 上級姓名
FROM employees e
LEFT JOIN employees m -- 自連接：將員工表同時作為員工和上級表使用ON e.manager_id = m.emp_id; -- 通過manager_id關聯上級信息
結果：
| employee | manager |
|---|---|
| CEO | NULL |
| CTO | CEO |
| 工程師 | CTO |
八、常見錯誤與解決方法
1. 笛卡爾積問題
錯誤示例：
SELECT * FROM users, orders; -- 未指定關聯條件
結果：用戶數 × 訂單數 條記錄（如2用戶×2訂單=4條）
正確寫法：
SELECT * FROM users
JOIN orders ON users.user_id = orders.user_id;
2. 別名使用不當
錯誤示例：
SELECT user_id FROM users u
JOIN orders o ON users.user_id = o.user_id; -- 錯誤：未使用別名
正確寫法：
SELECT u.user_id FROM users u
JOIN orders o ON u.user_id = o.user_id;
九、最佳實踐建議
- 優先使用 INNER JOIN：明確需要關聯數據時使用
- 慎用 RIGHT JOIN：可通過調換表順序改用 LEFT JOIN
- 使用表別名：提高可讀性（如
users u） - 關聯字段加索引：顯著提升 JOIN 性能
- 避免 SELECT：明確列出需要字段
