left join中右表条件必须写在on而非where，否则会丢失左表无匹配的行而等效于inner join；多表关联时各表逻辑删除字段须各自绑定on；复杂筛选应通过cte预计算以控制中间结果规模。
LEFT JOIN 里右表条件必须写在 ON 中
LEFT JOIN 的核心诉求是保留左表所有行，哪怕右表没匹配上。一旦把右表字段的过滤条件（比如 status = 'paid'）丢进 WHERE，结果就变成只留有匹配且满足条件的行——左表那些“空匹配”的记录全被删了，等效于 INNER JOIN。
常见错误现象：SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.status = 'paid'，执行后发现没下单的用户彻底消失。
-
ON中的条件参与关联决策：只决定“哪些右表行能连进来”，不删左表行 -
WHERE是连接完成后的全局筛选：只要含右表字段的非空判断（如o.status = 'paid'），NULL行全被踢掉 - 跨数据库行为一致：MySQL 8.0+、PostgreSQL、SQL Server 都严格按此语义执行，别指望优化器“自动修正”
多表 JOIN 时每个逻辑删除字段都要绑定对应 ON
当涉及 users → orders → items 这类三级关联，且每张业务表都有 is_deleted 字段时，不能靠最外层 WHERE 统一兜底。
错误写法：INNER JOIN items i ON o.id = i.order_id WHERE o.is_deleted = 0 AND i.is_deleted = 0 —— 优化器可能先连出已删除的 items，再筛，导致中间结果膨胀甚至逻辑错乱。
- 正确做法是每个被连接表的
is_deleted都写进它自己的ON子句：INNER JOIN orders o ON u.id = o.user_id AND o.is_deleted = 0，再接INNER JOIN items i ON o.id = i.order_id AND i.is_deleted = 0 - 若某表无逻辑删除字段（如
users），就不加；但未来加了就得同步补AND u.is_deleted = 0 - 视图或 ORM 中也得显式处理，不能依赖查询外的全局过滤
带排序优先级的过滤必须用 CTE 或子查询预计算
业务常要求“每个用户的最新已支付订单”，如果直接在 ON 里硬写 o.created_at = (SELECT MAX(...)) 或 o.rnk = 1，数据库可能先全量 JOIN 再过滤，中间数据暴涨，OOM 或超时风险极高。
真正可控的做法是把排序和筛选提前收口：
WITH latest_paid_order AS (
SELECT user_id, order_no, amount,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rnk
FROM orders
WHERE status = 'paid'
)
SELECT u.id, u.name, lpo.order_no
FROM users u
LEFT JOIN latest_paid_order lpo ON u.id = lpo.user_id AND lpo.rnk = 1;
-
WHERE status = 'paid'在 CTE 内提前缩小数据集，窗口函数计算量大幅下降 -
PARTITION BY user_id, ORDER BY created_at字段上必须有联合索引，否则ROW_NUMBER()会扫全表 - 千万级右表原始数据下，这一步通常能减少 90%+ 的 JOIN 中间结果
ON 中慎用 NOT 条件和 NULL 比较
ON t1.id = t2.t1_id AND t2.status != 'deleted' 看似合理，但实际会漏掉 t2.status IS NULL 的行——因为 NULL != 'deleted' 返回 UNKNOWN，不满足条件，这些行不会参与连接。
更隐蔽的问题是：某些数据库对 NOT IN 或 != 在 JOIN 条件中无法有效利用索引，执行计划可能退化为全表扫描。
- 需要排除特定值又兼容 NULL 时，显式写出：
t2.status IS NULL OR t2.status != 'deleted' - 避免在
ON中使用子查询或聚合函数，语法不合法且多数数据库直接报错 - 复杂分支逻辑（如按用户类型连不同表）不能用
CASE WHEN直接写进ON，得拆成多个LEFT JOIN+SELECT中用CASE选值
