在左连接里面，如果使用and筛选和用where筛选，区别是什么？

这是一个非常经典且重要的 SQL 问题。在左连接（LEFT JOIN）中使用 AND 和 WHERE 进行筛选，其逻辑和结果有本质的区别。

为了更直观地理解，我们通过一个例子来讲解。

示例数据

假设我们有两个表：

customers 表 (左表)

customer_id	name
1	张三
2	李四
3	王五

orders 表 (右表)

order_id	customer_id	amount	status
101	1	200	paid
102	1	150	pending
103	2	300	paid
104	3	50	cancelled

---

核心区别总结

• 在 ON 子句中使用 AND：这个条件是在连接时使用的。它会影响右表哪些记录可以匹配到左表，但不会影响左表主记录的返回。如果右表没有匹配的记录，左表的记录仍然会返回，只是右表部分为 NULL。
• 在 WHERE 子句中使用：这个条件是在连接完成后的结果集上使用的。它会过滤最终的结果集。如果 WHERE 条件过滤掉了某条左表记录（例如，因为右表的某个字段为 NULL），那么这条左表记录将不会出现在最终结果中。

---

场景分析

场景 1：在 ON 子句中使用 AND

假设我们想列出所有客户，同时只显示他们“已支付”（paid）的订单。我们仍然希望看到所有客户，即使他们没有已支付的订单。

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.amount,
    o.status
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id 
               AND o.status = 'paid'; -- 筛选条件在 ON 子句中

执行逻辑：

1. 从左表 customers 取出第一条记录（张三，id=1）。
2. 去右表 orders 寻找匹配记录，匹配条件是：customer_id 必须相等 并且 status 必须是 'paid'。
   • 张三有两条订单：101 (paid) 和 102 (pending)。
   • 只有订单 101 满足 customer_id=1 AND status='paid' 的条件。
3. 对李四（id=2）和王五（id=3）重复此过程。
   • 李四的订单 103 状态是 paid，匹配成功。
   • 王五的订单 104 状态是 cancelled，不匹配。但由于是左连接，王五这条客户记录依然会被保留，其订单信息为 NULL。

查询结果：

customer_id	name	order_id	amount	status
1	张三	101	200	paid
1	张三	NULL	NULL	NULL
2	李四	103	300	paid
3	王五	NULL	NULL	NULL

关键点： 左表 customers 的所有记录都出现了。ON ... AND ... 只是控制了右表 orders 中哪些记录有资格参与匹配。

---

场景 2：在 WHERE 子句中使用筛选

现在，假设我们想找出所有有已支付订单的客户。注意，这个需求本身就不再需要保留所有客户了。

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.amount,
    o.status
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id -- 连接条件只有 ID
WHERE 
    o.status = 'paid'; -- 筛选条件在 WHERE 子句中

执行逻辑：

1. 先进行标准的左连接，不关心订单状态。
   • 张三会匹配到两条订单（101 和 102）。
   • 李四匹配到一条订单（103）。
   • 王五匹配不到订单，结果为 NULL。
2. 连接完成后，得到一个临时的中间结果集。
3. 对这个中间结果集应用 WHERE o.status = 'paid' 条件。
   • 张三的订单 101 (paid) 符合条件。
   • 张三的订单 102 (pending) 不符合条件，被过滤掉。
   • 李四的订单 103 (paid) 符合条件。
   • 王五的记录因为 o.status 是 NULL，NULL = 'paid' 条件不成立，所以整条记录被过滤掉。

查询结果：

customer_id	name	order_id	amount	status
1	张三	101	200	paid
2	李四	103	300	paid

关键点： 王五（没有已支付订单的客户）从最终结果中消失了。此时的 LEFT JOIN 在效果上已经等同于 INNER JOIN，因为 WHERE 子句过滤掉了所有右表为 NULL 的行。

---

总结与类比

特性	ON ... AND ... (用于左连接)	WHERE ...
作用阶段	在连接过程中	在连接完成后
对左表的影响	不影响左表主记录的返回。保证左表所有记录都出现。	会影响。可能过滤掉左表的记录（当右表匹配字段为NULL时）。
对右表的影响	决定右表的哪条记录有资格与左表匹配。	在连接后的结果集中过滤掉不符合条件的行。
结果集性质	真正的“左连接”结果。	可能将左连接退化为内连接的效果。
常见用途	“我想列出所有A，顺便看看A中符合某条件的B”。例如：所有客户及其已支付订单。	“我想从A中找出那些在B中符合某条件的记录”。例如：找出有已支付订单的客户。

一个简单的类比：

• ON ... AND ... 就像是在一场相亲大会上，保证所有男士（左表）都能上台，但规定只能和穿红衣服的女士（右表）握手。即使某位男士没有和任何红衣女士握手，他依然在台上。
• WHERE ... 则是等所有男士和所有女士（不管穿什么衣服）都握完手后，只让那些最终和红衣女士握了手的男士留下来。没握到手的男士会被请下台。