SQL语句中left join后用on还是where的区别有哪些

# SQL语句中LEFT JOIN后用ON还是WHERE的区别有哪些

## 引言

在SQL查询中，JOIN操作是最常用的数据关联方式之一。其中LEFT JOIN（左连接）因其保留左表全部记录的特色被广泛使用。但很多开发者对LEFT JOIN后使用ON条件与WHERE条件的区别存在困惑，错误使用可能导致查询结果与预期不符。本文将深入探讨这两种写法的差异，帮助读者掌握正确用法。

## 一、基础概念回顾

### 1. LEFT JOIN的本质
LEFT JOIN会返回左表（FROM子句指定的表）的所有记录，即使右表中没有匹配的记录。当右表无匹配时，结果中右表字段显示为NULL。

### 2. ON条件的作用
ON是JOIN操作的一部分，用于指定两个表之间的关联条件。在LEFT JOIN中，ON条件决定如何从右表查找匹配记录。

### 3. WHERE条件的作用
WHERE是对整个结果集进行过滤，在所有JOIN操作完成后应用。

## 二、ON与WHERE在LEFT JOIN中的关键区别

### 1. 执行时机不同
- **ON条件**：在JOIN过程中应用，决定右表的匹配行为
- **WHERE条件**：在JOIN完成后应用，过滤最终结果

### 2. 对NULL值的影响
```sql
-- 示例1：ON条件
SELECT a.id, b.value 
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id AND b.status = 'active'

-- 示例2：WHERE条件 
SELECT a.id, b.value
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE b.status = 'active' OR b.id IS NULL

关键区别： - 示例1会返回所有a表记录，b表不满足status=‘active’的会被设为NULL - 示例2会过滤掉b表不满足status=‘active’且非NULL的记录

3. 结果集差异对比

假设有以下数据：

table_a        table_b
id | name     id | value  | status
---+------    ---+-------+--------
1  | Alice    1  | 100    | active
2  | Bob      1  | 200    | expired
3  | Carol    2  | 300    | active

不同查询的结果对比：

三、典型应用场景

1. 应使用ON条件的场景

• 需要保留左表所有记录时
• 右表的过滤条件不应影响左表记录显示
• 多表关联时的中间表过滤

-- 获取所有客户及其最近一笔订单（即使没有订单）
SELECT c.customer_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id 
                  AND o.order_date = (
                      SELECT MAX(order_date) 
                      FROM orders 
                      WHERE customer_id = c.customer_id
                  )

2. 应使用WHERE条件的场景

• 需要同时过滤左右表数据时
• 明确要排除某些记录的情况

-- 只查询有活跃订单的客户
SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'active'

3. 组合使用的场景

-- 查询所有客户及他们的活跃订单（没有活跃订单的客户也显示）
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id 
                   AND o.status = 'active'
WHERE c.region = 'North' -- 对左表的基础过滤

四、性能考量

1. 索引利用：

   • ON条件中的列通常能利用JOIN索引
   • WHERE条件中的列可以使用单表索引

2. 执行计划差异：

   • 数据库优化器可能对ON和WHERE生成不同的执行计划
   • 复杂的ON条件可能导致嵌套循环，而WHERE通常最后应用

3. 大数据量下的建议： “`sql – 不推荐：WHERE过滤右表 SELECT a.* FROM big_table a LEFT JOIN huge_table b ON a.id = b.id WHERE b.create_time > ‘2023-01-01’ – 先JOIN再过滤，性能差

– 推荐：ON条件提前过滤 SELECT a.* FROM big_table a LEFT JOIN ( SELECT * FROM huge_table WHERE create_time > ‘2023-01-01’ ) b ON a.id = b.id

## 五、常见误区与陷阱

1. **错误认为WHERE和ON等效**：
   - 误用WHERE会导致LEFT JOIN退化为INNER JOIN效果

2. **NULL值处理不当**：
   ```sql
   -- 错误写法：会排除右表为NULL的记录
   SELECT * FROM table_a a
   LEFT JOIN table_b b ON a.id = b.id
   WHERE b.value > 100  -- 漏掉b.id为NULL的记录
   
   -- 正确写法：
   WHERE b.value > 100 OR b.id IS NULL

1. 多条件混合时的逻辑混乱：

   
   -- 混淆逻辑的示例
   SELECT a.* FROM orders a
   LEFT JOIN items b ON a.order_id = b.order_id
                   AND a.status = 'completed' -- 这个应放在WHERE
   WHERE b.price > 100
   

六、最佳实践建议

1. 遵循以下原则：

   • 保留左表全部记录 → 使用ON
   • 过滤最终结果 → 使用WHERE
   • 两者都需要 → 组合使用

2. 编写清晰的SQL：

   • 将关联条件放在ON子句
   • 将过滤条件放在WHERE子句
   • 对复杂条件添加注释

3. 测试验证：

   -- 验证查询是否返回预期记录数
   EXPLN ANALYZE SELECT ...
   -- 检查执行计划中的"Rows Removed by Filter"
   

结语

理解LEFT JOIN中ON与WHERE的区别是写出正确SQL的重要基础。关键要记住：ON决定如何连接，WHERE决定显示什么。在实际开发中，应根据业务需求选择合适的方式，并通过执行计划验证查询效率。掌握这一区别将显著提高复杂查询的准确性和性能。

作者提示：当不确定该用ON还是WHERE时，可以自问：”这个条件是否应该影响左表记录的保留？”如果答案是否定的，就应该放在ON条件中。 “`