SQL中WHERE与ON的区别详解

这是一个SQL初学者非常常见且重要的问题。WHERE 和 ON 都用于过滤数据，但它们的使用场景和目的有本质区别。

简单来说：

• ON：用于连接表，指定两个表如何链接在一起。它主要与 JOIN 一起使用。
• WHERE：用于过滤记录，在连接完成后（或在单表查询中）从结果集中筛选出满足条件的行。

下面我们通过一个详细的对比和例子来理解。

---

详细对比

特性	ON 子句	WHERE 子句
主要用途	定义表之间的连接条件。	对结果集进行过滤。
使用场景	必须与 JOIN（如 INNER JOIN, LEFT JOIN）一起使用。	可用于任何 SELECT、UPDATE、DELETE 语句。
执行顺序	在连接过程中执行。它决定了哪些行有资格从连接的表中组合出来。	通常在连接操作之后执行（对已连接好的结果集进行过滤）。
对 LEFT/RIGHT JOIN 的影响	关键区别！ 在左外连接中，ON 条件过滤右表，但不保证左表的所有行都被保留。要过滤左表，需要额外条件。	在左外连接中，WHERE 条件会移除所有不满足条件的行，包括那些因为左连接而生成的NULL行，这可能会将外连接退化为内连接。

---

举例说明

假设我们有两个表：

Employees 表 (员工表)

employee_id	name	department_id
1	张三	101
2	李四	102
3	王五	NULL

Departments 表 (部门表)

department_id	department_name
101	销售部
102	技术部
103	市场部

场景一：INNER JOIN（内连接）

在这个场景下，将条件放在 ON 还是 WHERE 中，结果通常是一样的。

查询1：使用 ON

SELECT e.name, d.department_name
FROM Employees e
INNER JOIN Departments d 
ON e.department_id = d.department_id; -- 连接条件

查询2：使用 ON 和 WHERE

SELECT e.name, d.department_name
FROM Employees e
INNER JOIN Departments d 
ON e.department_id = d.department_id -- 连接条件
WHERE e.department_id = 101; -- 过滤条件

• 查询1结果：连接所有有部门的员工。

  name	department_name
  张三	销售部
  李四	技术部

• 查询2结果：连接后，只保留部门ID为101的员工。

  name	department_name
  张三	销售部

在INNER JOIN中，数据库优化器通常会将ON中的连接条件和其他过滤条件一起处理，所以最终效果相同。但逻辑上，ON负责连接，WHERE负责最终过滤。

场景二：LEFT JOIN（左外连接）【核心区别】

这是体现两者区别最明显的场景。

查询3：条件在 ON 子句中

SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d 
ON e.department_id = d.department_id -- 连接条件
AND d.department_name = '技术部'; -- 也是连接条件的一部分

• 说明：ON 子句的意思是：“将员工表与部门表连接，但只连接那些部门ID匹配并且部门名是‘技术部’的部门”。
• 结果：左表 Employees 的所有记录都会被保留。对于“李四”，他满足ON的所有条件，所以成功连接。对于“张三”和“王五”，没有满足ON条件的部门与之匹配，所以部门信息为NULL。

  name	department_name
  张三	NULL
  李四	技术部
  王五	NULL

查询4：条件在 WHERE 子句中

SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d 
ON e.department_id = d.department_id -- 连接条件
WHERE d.department_name = '技术部'; -- 连接后过滤

• 说明：先进行左连接（连接所有部门ID匹配的记录），得到一个中间结果集，然后WHERE子句对这个结果集进行过滤，只保留部门名为‘技术部’的行。
• 结果：WHERE 条件会过滤掉所有 department_name 不是 ‘技术部’ 或为 NULL 的行。这导致“张三”和“王五”被移除，LEFT JOIN 的效果变得和 INNER JOIN 一样。

  name	department_name
  李四	技术部

查询5：在 WHERE 中过滤左表

SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d 
ON e.department_id = d.department_id
WHERE e.name = '张三'; -- 过滤左表，这是安全的

• 说明：在左连接后，过滤左表的列是安全的，因为它不会导致左表的行丢失。
• 结果：

  name	department_name
  张三	销售部

---

总结与最佳实践

1. 根本区别：

   • ON 是 “连接器”，负责匹配。
   • WHERE 是 “过滤器”，负责筛选。

2. 关键影响：

   • 在外连接（LEFT/RIGHT JOIN）中，ON 和 WHERE 的放置会导致完全不同的结果。
   • 如果你想在保留主表（如左表）所有记录的同时，对从表（如右表）的连接行为进行限制，请将条件放在 ON 中。
   • 如果你希望对最终连接好的整个结果集进行过滤，请将条件放在 WHERE 中。

3. 最佳实践：

   • 连接条件永远放在 ON 子句中。
   • 普通的行级过滤条件放在 WHERE 子句中。
   • 这样写逻辑清晰，易于理解和维护，也能避免在外连接中产生意想不到的结果。