ON 和 WHERE 都用于筛选数据，但它们的执行时机和应用范围不同，理解这一点对编写正确的查询至关重要。
核心区别总结
| 特性 |
ON 子句 |
WHERE 子句 |
|---|
| 执行时机 |
在 JOIN 过程中过滤 |
在 JOIN 完成后过滤 |
| 作用范围 |
控制如何连接表 |
过滤最终结果集 |
| 对 NULL 的影响 |
影响外连接的结果 |
通常会将 NULL 过滤掉 |
| 可读性 |
连接条件放在这里更清晰 |
普通过滤条件放在这里 |
详细解析
1. INNER JOIN 中的表现
在 INNER JOIN 中，两者效果通常相同，因为连接后再过滤和过滤后再连接结果一致：
-- 这两种写法在 INNER JOIN 中结果相同
SELECT *
FROM A
INNER JOIN B ON A.id = B.id AND A.status = 'active';
SELECT *
FROM A
INNER JOIN B ON A.id = B.id
WHERE A.status = 'active';
2. LEFT/RIGHT JOIN 中的关键差异
这是最能体现区别的场景：
-- 示例数据
-- 表A: (1, 'A1'), (2, 'A2'), (3, 'A3')
-- 表B: (1, 'B1', 'active'), (2, 'B2', 'inactive')
-- 情况1：条件放在 ON 子句
SELECT A.id, A.name, B.info
FROM A
LEFT JOIN B ON A.id = B.id AND B.status = 'active';
-- 结果：所有A记录都会出现，B匹配不到则为NULL
-- (1, 'A1', 'B1'), (2, 'A2', NULL), (3, 'A3', NULL)
-- 情况2：条件放在 WHERE 子句
SELECT A.id, A.name, B.info
FROM A
LEFT JOIN B ON A.id = B.id
WHERE B.status = 'active';
-- 结果：LEFT JOIN 变成了 INNER JOIN 的效果
-- 因为 WHERE 会过滤掉 B.info 为 NULL 的行
-- (1, 'A1', 'B1') -- 只有这一条！
3. 执行顺序理解
SQL 的执行逻辑顺序是：
FROM 和 JOIN（使用 ON 条件决定如何连接）
WHERE（过滤已连接的结果）
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
实际应用建议
使用 ON 的情况：
连接条件：关联两个表的字段
ON A.user_id = B.id
外连接的特殊过滤：不想影响主表的所有行
LEFT JOIN orders ON users.id = orders.user_id
AND orders.date > '2024-01-01' -- 仍然返回所有用户
使用 WHERE 的情况：
结果集过滤：对所有数据统一过滤
WHERE users.is_active = 1
将外连接转为内连接：明确需要过滤掉 NULL
LEFT JOIN departments ON ...
WHERE departments.id IS NOT NULL -- 等价于 INNER JOIN
最佳实践
连接条件放 ON：所有与表间关系相关的条件
过滤条件放 WHERE：与具体业务逻辑相关的过滤
外连接特别注意：思考是否要保留主表的所有记录
提高可读性：让他人一眼能看出连接逻辑和过滤逻辑
-- 清晰的写法示例
SELECT u.name, o.order_date, p.product_name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
AND o.status = 'completed' -- 连接时的附加条件
LEFT JOIN products p ON o.product_id = p.id
WHERE u.created_at > '2023-01-01' -- 对最终结果的过滤
AND u.country = 'US'
ORDER BY o.order_date DESC;
记住：ON 决定如何连接，WHERE 决定显示什么。在外连接中，这个区别尤为关键，可能完全改变查询结果。
