MySQL 多表聯查 (JOIN) 完整語法與視覺化解析

多表查詢是關係型資料庫核心能力。本文系統整理內連接、左/右連接、多表鏈式連接語法與視覺化理解。

核心 JOIN 類型

類型	關鍵字	結果集	Venn 圖
內連接	INNER JOIN / JOIN	兩表匹配的行	交集 A∩B
左連接	LEFT JOIN / LEFT OUTER JOIN	左表所有行 + 右表匹配行	左集合 A 全部
右連接	RIGHT JOIN / RIGHT OUTER JOIN	右表所有行 + 左表匹配行	右集合 B 全部
全連接	FULL JOIN (MySQL 不支援)	兩表所有行	並集 A∪B
交叉連接	CROSS JOIN	笛卡爾積	A×B

基本語法模板

SELECT 選取列表
FROM 表1 別名1
JOIN類型 表2 別名2 ON 連接條件
[JOIN類型 表3 別名3 ON 連接條件 ...]
WHERE 過濾條件
GROUP BY 分組列表
HAVING 分組過濾
ORDER BY 排序列表
LIMIT 限制行數;

實戰範例：訂單與用戶表

-- 表結構
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

1. 內連接：只查有訂單的用戶

-- 隱式內連接 (舊語法，不推薦)
SELECT u.name, o.amount, o.status
FROM users u, orders o
WHERE u.id = o.user_id;

-- 顯式內連接 (標準語法，推薦)
SELECT u.name, o.amount, o.status
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

2. 左連接：查所有用戶及其訂單 (無訂單顯示 NULL)

SELECT u.name, o.id AS order_id, o.amount, o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 結果範例：
-- name    order_id  amount   status
-- 張三    101       99.00    paid
-- 李四    102       199.00   shipped
-- 王五    NULL      NULL     NULL  ← 無訂單用戶

3. 右連接：查所有訂單及用戶 (理論上同左連接，表順序互換)

SELECT u.name, o.id, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- 等價於
SELECT u.name, o.id, o.amount
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

4. 多表鏈式連接：用戶 → 訂單 → 訂單項 → 商品

SELECT u.name, o.id AS order_id, oi.quantity, p.name AS product, p.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC;

進階技巧

多條件連接

SELECT * FROM orders o
INNER JOIN users u ON u.id = o.user_id AND u.status = 'active'
WHERE o.created_at > '2023-01-01';

子查詢作為連接源 (Derived Table)

SELECT u.name, recent_orders.order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY user_id
) recent_orders ON u.id = recent_orders.user_id;

USING 簡寫 (欄位名相同時)

SELECT * FROM orders
INNER JOIN users USING (user_id);
-- 等價於 ON orders.user_id = users.user_id

NATURAL JOIN (自動匹配同名欄位，不推薦生產用)

SELECT * FROM orders NATURAL JOIN users;

效能優化關鍵

MySQL 8.0 新特性：CTE 簡化複雜聯查

WITH recent_users AS (
    SELECT id, name FROM users WHERE created_at > '2023-01-01'
), paid_orders AS (
    SELECT user_id, SUM(amount) AS total FROM orders WHERE status='paid' GROUP BY user_id
)
SELECT u.name, COALESCE(o.total, 0) AS total_paid
FROM recent_users u
LEFT JOIN paid_orders o ON u.id = o.user_id;

熟練掌握 JOIN 類型與優化原則，是編寫高效 SQL 的核心技能。