SQL语句的ON和Where关键词生效时机或作用场景

目录

1. 筛选顺序

逻辑执行顺序

关键区别

2. 数据量影响

ON 条件的作用

WHERE 条件的作用

3. 性能优化建议

4. 总结

1. 筛选顺序

逻辑执行顺序

SQL 查询的逻辑执行顺序如下（实际执行可能因优化器调整）：

1. FROM 和 JOIN ：确定表连接关系。
2. ON ：在连接时过滤连接条件（仅用于 JOIN 操作）。
3. WHERE ：对连接后的结果集进行过滤。

关键区别

• ON ：在连接阶段应用条件，用于筛选参与连接的行 。

  • 对于 INNER JOIN，ON 和 WHERE 的条件位置可能不影响结果，但可能影响中间数据量。
  • 对于 OUTER JOIN（如 LEFT JOIN），ON 条件会影响连接结果，而 WHERE 条件会过滤最终结果（可能导致 OUTER JOIN 的 NULL 行被过滤）。

• WHERE ：在连接完成后对最终结果集 进行过滤。

2. 数据量影响

ON 条件的作用

• 减少中间数据量 ：在连接阶段提前过滤数据，降低后续操作的数据量

-- 示例：提前过滤 orders 表
SELECT * 
FROM users 
LEFT JOIN orders 
  ON users.id = orders.user_id 
  AND orders.create_time > '2023-01-01'; -- ON 条件过滤

• 这里 orders 表在连接前就被过滤，减少参与连接的数据量。

WHERE 条件的作用

• 过滤最终结果 ：在连接完成后过滤，可能需要处理更大的中间结果。

-- 示例：WHERE 过滤最终结果
SELECT * 
FROM users 
LEFT JOIN orders 
  ON users.id = orders.user_id 
WHERE orders.create_time > '2023-01-01'; -- WHERE 过滤

• 此时 WHERE 会过滤掉所有 orders.create_time <= '2023-01-01' 的行，包括 LEFT JOIN 产生的 NULL 行（可能导致结果与预期不符）。

3. 性能优化建议

1. 优先使用 ON 过滤连接条件 ：

   • 在 JOIN 时通过 ON 尽早减少参与连接的数据量，提升效率。
   • 例如：在 INNER JOIN 中，将过滤条件放在 ON 子句中。

2. 注意 OUTER JOIN 的陷阱 ：

   • WHERE 条件可能意外过滤掉 OUTER JOIN 产生的 NULL 行。
   • 示例：

     SELECT * 
     FROM users 
     LEFT JOIN orders 
       ON users.id = orders.user_id 
     WHERE orders.status = 'paid'; -- 错误！会过滤掉没有订单的用户

     正确做法是将 orders.status = 'paid' 放在 ON 子句中。

3. 数据库优化器的影响 ：

   • 现代数据库（如 MySQL、PostgreSQL）的优化器可能自动调整条件顺序，但显式使用 ON 和 WHERE 仍能提高可读性和可控性。

4. 总结

最佳实践 ：

• 在 JOIN 中使用 ON 过滤连接条件。
• 在 WHERE 中过滤最终结果的通用条件。
• 使用 EXPLAIN 分析执行计划，验证优化效果。