5 分钟搞懂 JOIN

示例数据

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(64));
CREATE TABLE orders (id INT PRIMARY KEY, user_id INT, amount INT);

INSERT INTO users VALUES (1,'alice'),(2,'bob'),(3,'carol');
INSERT INTO orders VALUES (10,1,100),(11,1,200),(12,2,50);
-- carol 没下过单

INNER JOIN

两边都有匹配才返回。

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- alice | 100
-- alice | 200
-- bob   | 50
-- (carol 不出现)

LEFT JOIN

保留左表所有行，右表没匹配则 NULL。

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

-- alice | 100
-- alice | 200
-- bob   | 50
-- carol | NULL

RIGHT JOIN

A RIGHT JOIN B ≡ B LEFT JOIN A。几乎没人写 RIGHT JOIN，统一用 LEFT JOIN 调换顺序更可读。

FULL JOIN

MariaDB 不支持 FULL OUTER JOIN。用 LEFT + UNION 模拟：

SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.name, o.amount FROM users u RIGHT JOIN orders o ON o.user_id = u.id;

CROSS JOIN（笛卡尔积）

SELECT * FROM users CROSS JOIN orders;  -- 3 × 3 = 9 行

通常是 BUG——除非你真的需要笛卡尔积（比如生成日期 × 用户的矩阵）。

SELF JOIN

同一张表 JOIN 自己。常用例：找上下级。

CREATE TABLE employees (id INT, name VARCHAR(64), boss_id INT);

SELECT e.name AS emp, b.name AS boss
FROM employees e
LEFT JOIN employees b ON b.id = e.boss_id;

JOIN 性能要点

1. 用索引

JOIN 的 ON 字段必须有索引，否则全表扫描互相对比。

INDEX idx_user (user_id) ON orders;

2. 看 EXPLAIN

EXPLAIN SELECT u.name, COUNT(*)
FROM users u JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

注意 type、rows、Extra。type=ALL 是 ❌。

3. JOIN 顺序

MariaDB 优化器会自动重排 JOIN 顺序，挑选最小代价。但你可以用 STRAIGHT_JOIN 强制按写的顺序：

SELECT STRAIGHT_JOIN ... FROM small_table JOIN big_table ...;

一般不需要——除非你确定优化器选错了（看 ANALYZE 验证）。

4. 避免 N+1

# ❌ N+1：1 次查 N 个用户 + N 次查每个用户的订单
users = db.query('SELECT * FROM users')
for u in users:
    orders = db.query(f'SELECT * FROM orders WHERE user_id = {u.id}')

# ✅ 一次 JOIN
rows = db.query('''
  SELECT u.id, u.name, o.id AS order_id, o.amount
  FROM users u LEFT JOIN orders o ON o.user_id = u.id
''')

详见事故合集 → N+1 把数据库打挂。

5. JOIN 的等价改写

-- IN 子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- 等价的 JOIN
SELECT DISTINCT u.* FROM users u JOIN orders o ON o.user_id = u.id;
-- 等价的 EXISTS（最快，因为短路）
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

经验：大表用 EXISTS 通常最快。

JOIN 算法

MariaDB 11.x 支持三种 JOIN 算法（看 EXPLAIN 的 Extra）：

算法	适用	启用
Nested Loop（默认）	小表 / 索引完美	总是
Hash Join	大表 + 没索引	MariaDB 10.6+ 自动
Block Nested Loop	中等表无索引	自动

Hash Join 是 MariaDB 的杀手锏——在没索引的场景比 MySQL 8 快很多。详见优化器深度。

常见错误

1. 忘写 ON：等同 CROSS JOIN。MariaDB 会 warning 但不报错。

2. WHERE 写在了 LEFT JOIN 的右表条件：会把 LEFT 退化成 INNER。要写在 ON 里。

-- ❌ 退化成 INNER：carol 不见了
SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.amount > 0;
-- ✅ 保持 LEFT
SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.amount > 0;

3. 三表以上 JOIN 不写明顺序：可读性炸了。用 CTE 拆开：

WITH active_users AS (SELECT * FROM users WHERE last_login > NOW() - INTERVAL 30 DAY)
SELECT au.name, COUNT(o.id)
FROM active_users au
LEFT JOIN orders o ON o.user_id = au.id
GROUP BY au.id;