on用于指定join的连接条件，直接影响连接结果；where用于过滤连接后的结果集。on决定哪些行在连接时被包含，而where筛选最终输出的行。在left join中，将右表条件放在where可能导致退化为inner join，影响结果准确性。性能上，on可能减少连接数据量，但需结合索引和dbms特性权衡使用。
sql中ON和WHERE的区别在于它们在JOIN操作中的作用不同：ON指定连接条件，决定如何将两个表连接起来；WHERE则用于过滤连接后的结果集，筛选出满足特定条件的行。
连接条件与过滤条件的本质差异在于，ON影响连接的结果，而WHERE影响最终的输出。
ON子句和WHERE子句，是SQL查询中经常让人困惑的点。它们都用于条件过滤，但作用时机和范围却大相径庭。理解它们的差异，能写出更高效、更准确的sql语句。
为什么理解ON和WHERE的区别至关重要？
搞清楚ON和WHERE的区别，能避免一些常见的SQL错误，比如数据丢失或不准确的结果。尤其是在处理外连接时，ON和WHERE的行为差异会非常明显。错误的使用可能导致查询返回意外的结果，甚至影响业务逻辑。
ON子句：连接的桥梁
ON子句主要用于指定JOIN操作的连接条件。它告诉数据库如何将两个或多个表中的行连接起来。ON子句在连接过程中起作用，决定哪些行可以被连接。
例如，假设有两个表：orders（订单）和customers（客户）。orders表包含customer_id（客户ID）字段，customers表包含id（客户ID）字段。可以使用ON子句将这两个表连接起来：
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
在这个例子中，ON orders.customer_id = customers.id指定了连接条件。只有当orders表中的customer_id与customers表中的id相等时，对应的行才会被连接起来。
需要特别注意的是，在使用LEFT JOIN、RIGHT JOIN或FULL OUTER JOIN时，ON子句的行为会直接影响结果集。对于LEFT JOIN，即使右表（customers）中没有与左表（orders）匹配的行，左表的所有行仍然会出现在结果集中，右表对应的列会填充NULL值。而ON子句决定了哪些右表的行会被用来填充这些NULL值。
WHERE子句：结果的过滤器
WHERE子句用于在连接操作完成后，对结果集进行过滤。它根据指定的条件筛选出满足条件的行。WHERE子句作用于最终的结果集，决定哪些行会被返回。
继续上面的例子，如果只想获取订单金额大于100的订单信息，可以使用WHERE子句：
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id WHERE orders.amount > 100;
在这个例子中，WHERE orders.amount > 100指定了过滤条件。只有订单金额大于100的行才会被返回。
WHERE子句可以包含多个条件，使用AND和OR运算符进行组合。这使得可以根据复杂的逻辑筛选出满足特定条件的行。
ON与WHERE在不同JOIN类型下的行为差异
ON和WHERE在不同的JOIN类型（INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN）下的行为有所不同。理解这些差异对于编写正确的SQL查询至关重要。
-
INNER JOIN：对于INNER JOIN，ON和WHERE的效果在某些情况下可能看起来相似，但本质上仍然不同。ON决定哪些行被连接，WHERE过滤连接后的结果。将条件放在ON或WHERE中，最终结果可能会相同，但执行计划可能会有所不同，影响性能。
-
LEFT JOIN：对于LEFT JOIN，ON和WHERE的区别非常明显。ON子句决定了右表（B）中哪些行与左表（A）匹配，如果没有匹配的行，右表的列会填充NULL值。而WHERE子句则在连接完成后，对整个结果集进行过滤。如果将右表（B）的过滤条件放在WHERE子句中，可能会导致LEFT JOIN退化为INNER JOIN，因为WHERE子句会过滤掉右表列为NULL的行。
-
RIGHT JOIN：RIGHT JOIN与LEFT JOIN类似，只是左右表的位置互换。
-
FULL OUTER JOIN：FULL OUTER JOIN返回左表和右表的所有行。对于没有匹配的行，对应的列会填充NULL值。ON子句决定了哪些行被连接，WHERE子句过滤连接后的结果。
为了更好地理解ON和WHERE在不同JOIN类型下的行为差异，可以参考以下示例：
假设有两个表：employees（员工）和departments（部门）。
employees表：
| id | name | department_id |
|---|---|---|
| 1 | Alice | 1 |
| 2 | Bob | 2 |
| 3 | Charlie | NULL |
departments表：
| id | name |
|---|---|
| 1 | Engineering |
| 2 | Marketing |
| 3 | Sales |
以下是一些示例查询：
- INNER JOIN with ON
SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.id;
结果：
| employees.id | name | department_id | departments.id | name |
|---|---|---|---|---|
| 1 | Alice | 1 | 1 | Engineering |
| 2 | Bob | 2 | 2 | Marketing |
- LEFT JOIN with ON
SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
结果：
| employees.id | name | department_id | departments.id | name |
|---|---|---|---|---|
| 1 | Alice | 1 | 1 | Engineering |
| 2 | Bob | 2 | 2 | Marketing |
| 3 | Charlie | NULL | NULL | NULL |
- LEFT JOIN with ON and WHERE
SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.id WHERE departments.id IS NULL;
结果：
| employees.id | name | department_id | departments.id | name |
|---|---|---|---|---|
| 3 | Charlie | NULL | NULL | NULL |
这个查询返回了没有分配到任何部门的员工。
SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.id WHERE departments.name = 'Engineering';
结果：
| employees.id | name | department_id | departments.id | name |
|---|---|---|---|---|
| 1 | Alice | 1 | 1 | Engineering |
在这个例子中，WHERE departments.name = ‘Engineering’ 实际上将 LEFT JOIN 转换为了 INNER JOIN，因为只有部门名称为 ‘Engineering’ 的行才会被返回。
性能考量：ON还是WHERE？
在性能方面，ON和WHERE的选择可能会影响查询的执行计划。通常情况下，数据库优化器会尝试在连接之前应用ON子句中的条件，以减少需要连接的行数。而WHERE子句则在连接完成后应用。
因此，将过滤条件放在ON子句中，特别是对于大表连接，可能会提高查询性能。但是，这并不意味着总是应该将所有条件都放在ON子句中。需要根据具体的查询和数据特点进行权衡。
例如，如果ON子句中的条件涉及到多个表的列，数据库可能无法有效地利用索引。在这种情况下，将条件放在WHERE子句中可能更好。
此外，不同的数据库管理系统（DBMS）对于ON和WHERE的优化策略可能有所不同。因此，最好使用数据库提供的性能分析工具（如EXPLaiN语句）来评估不同写法的性能。
总结
ON子句用于指定JOIN操作的连接条件，决定如何将两个或多个表中的行连接起来。WHERE子句用于在连接操作完成后，对结果集进行过滤，筛选出满足特定条件的行。理解它们的差异，能避免一些常见的SQL错误，写出更高效、更准确的SQL语句。在实际应用中，需要根据具体的查询和数据特点进行权衡，选择合适的写法。
