`LEFT JOIN` 的 `WHERE` 子句条件对结果集的影响分析

好的，各位观众，各位老铁，欢迎来到今天的“数据库奇妙夜”！我是你们的老朋友，人称“数据库小诸葛”的程序猿阿甘。今晚，咱们不聊风花雪月，不谈人生理想，就来聊聊数据库里那些让人抓耳挠腮的小细节，特别是 LEFT JOIN 的 WHERE 子句，这玩意儿，用好了是神兵利器，用不好，那就是埋雷专家，一不小心就把你的数据炸得面目全非。💥

咱们先来个小剧场，模拟一下程序员小李的日常：

场景：程序员小李的崩溃日常

小李：(挠头) 领导说要统计所有客户的订单情况，包括没有下过单的客户，都要显示出来。这还不简单？LEFT JOIN 走起！

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM
    customers c
LEFT JOIN
    orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id, c.customer_name;

小李：(得意) 看，完美！所有客户都出来了，没下单的 order_count 是0，一切正常！

领导：(皱眉) 小李啊，你这结果不对啊，我只要查上海的客户，而且订单金额大于100的才算有效订单。

小李：(自信) 简单！WHERE 子句加起来！

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM
    customers c
LEFT JOIN
    orders o ON c.customer_id = o.customer_id
WHERE
    c.city = '上海' AND o.order_amount > 100
GROUP BY
    c.customer_id, c.customer_name;

小李：(信心满满地运行) 领导，搞定！

领导：(暴怒) 小李！你这结果怎么回事？怎么只有下了单的上海客户？没下单的上海客户呢？我要的是所有上海客户的订单情况，哪怕他没下过单！

小李：(一脸懵逼) 这…这…这不科学啊！我明明用了 LEFT JOIN 啊！

(小李内心OS：我的头发啊！又要少几根了！😱)

阿甘：(清清嗓子) 好了，小李的惨剧就到这里。各位是不是觉得这个场景似曾相识？别慌，今天阿甘就来给大家拨开云雾，讲清楚 LEFT JOIN 的 WHERE 子句到底是怎么工作的，以及如何避免掉进坑里。

一、 LEFT JOIN 的基础知识回顾：左边的才是真爱！

首先，咱们来复习一下 LEFT JOIN 的基本概念。LEFT JOIN，顾名思义，就是以左边的表为基础，尽可能地把右边的表“连接”过来。

左表 (Left Table)： LEFT JOIN 关键字左边的表，也叫“基表”。重点是，左表的所有记录都会出现在结果集中，不管右表有没有匹配的记录。
右表 (Right Table)： LEFT JOIN 关键字右边的表。
ON 条件： ON 后面跟着的是连接条件，告诉数据库如何把左右两张表连接起来。
结果集： 如果左表的某条记录在右表中找到了匹配的记录，那么就把它们合并成一行；如果左表的某条记录在右表中找不到匹配的记录，那么右表对应的列就填充 NULL 值。

可以把 LEFT JOIN 想象成一场舞会。左表是舞池里已经跳舞的人，右表是等待邀请的人。ON 条件就是邀请的规则。

如果左边的人邀请到了右边的人，那他们就配对成功，一起翩翩起舞（合并成一行）。💃🕺
如果左边的人没人邀请，那他就只能孤芳自赏，独自跳舞（右表的列填充 NULL）。 🕺孤单寂寞冷…

二、 WHERE 子句的威力：过滤，过滤，还是过滤！

WHERE 子句的作用很简单，就是对结果集进行过滤，只保留满足条件的记录。就像一个严格的门卫，不符合条件的人，一律不让进。 👮

三、 WHERE 子句与 LEFT JOIN 的爱恨情仇：一场美丽的误会

问题就出在这里：当 WHERE 子句和 LEFT JOIN 联手的时候，它们的行为会变得有点…微妙。

情况一：WHERE 子句只包含左表的条件

如果 WHERE 子句只包含左表的条件，那么它会先对左表进行过滤，然后再进行 LEFT JOIN。这种情况下，没啥问题，一切都在你的掌控之中。

比如：
```
SELECT
    c.customer_id,
    c.customer_name,
    o.order_id
FROM
    customers c
LEFT JOIN
    orders o ON c.customer_id = o.customer_id
WHERE
    c.city = '上海';
```
这个查询会先筛选出所有上海的客户，然后再把他们的订单信息 LEFT JOIN 过来。即使某个上海客户没有下过单，他/她也会出现在结果集中，只是 order_id 是 NULL。
情况二：WHERE 子句包含右表的条件

这就是坑爹的地方了！如果 WHERE 子句包含右表的条件，那么它的行为会变得…像 INNER JOIN！

为什么呢？

因为 WHERE 子句会对 LEFT JOIN 产生的结果集进行过滤。如果右表的条件不满足（比如 o.order_amount > 100），那么 o.order_amount 就会是 NULL。而 NULL 和任何值的比较结果都是 UNKNOWN，UNKNOWN 在 WHERE 子句中会被当做 FALSE 处理。

也就是说，如果右表没有匹配的记录，那么右表的列就会是 NULL，WHERE 子句中的右表条件就会变成 NULL > 100，结果是 UNKNOWN，这条记录就会被过滤掉！

这就解释了为什么小李的查询会丢失没有下单的上海客户：
```
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM
    customers c
LEFT JOIN
    orders o ON c.customer_id = o.customer_id
WHERE
    c.city = '上海' AND o.order_amount > 100
GROUP BY
    c.customer_id, c.customer_name;
```
o.order_amount > 100 这个条件把所有没有下单的上海客户都给过滤掉了，因为它们的 o.order_amount 是 NULL。

四、如何避免掉坑？把右表的条件放到 ON 子句里！

既然 WHERE 子句会把 LEFT JOIN 变成 INNER JOIN，那怎么办呢？答案很简单：把右表的条件放到 ON 子句里！

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM
    customers c
LEFT JOIN
    orders o ON c.customer_id = o.customer_id AND o.order_amount > 100
WHERE
    c.city = '上海'
GROUP BY
    c.customer_id, c.customer_name;

这样做，o.order_amount > 100 就变成了 LEFT JOIN 的连接条件的一部分。只有当订单金额大于 100 的时候，才会把订单信息连接过来。如果订单金额小于等于 100，或者客户没有下单，那么 order_id 仍然是 NULL，但是客户的信息仍然会保留在结果集中。

总结一下：

WHERE 子句用于过滤整个结果集。
LEFT JOIN 的目的是保留左表的所有记录。
如果 WHERE 子句包含右表的条件，那么它会把 LEFT JOIN 变成 INNER JOIN。
要把右表的条件放到 ON 子句里，才能保证 LEFT JOIN 的行为符合预期。

五、进阶技巧： COALESCE 函数的妙用

有时候，我们需要对 NULL 值进行处理，比如把 NULL 转换成 0，或者转换成其他有意义的值。这时候，COALESCE 函数就派上用场了。

COALESCE(value1, value2, ...) 函数会返回第一个不为 NULL 的值。如果所有的值都是 NULL，那么它会返回 NULL。

比如，我们可以用 COALESCE 函数来统计每个客户的订单总金额，如果客户没有下过单，那么订单总金额就显示为 0：

SELECT
    c.customer_id,
    c.customer_name,
    COALESCE(SUM(o.order_amount), 0) AS total_order_amount
FROM
    customers c
LEFT JOIN
    orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id, c.customer_name;

六、案例分析：用 LEFT JOIN 解决实际问题

咱们再来几个案例，巩固一下今天学到的知识。

案例一：统计每个部门的员工数量，包括没有员工的部门

假设我们有两张表：departments 和 employees。

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    department_id INT
);

要统计每个部门的员工数量，包括没有员工的部门，可以用下面的 SQL 语句：

SELECT
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM
    departments d
LEFT JOIN
    employees e ON d.department_id = e.department_id
GROUP BY
    d.department_id, d.department_name;

案例二：查找所有没有下过单的客户

SELECT
    c.customer_id,
    c.customer_name
FROM
    customers c
LEFT JOIN
    orders o ON c.customer_id = o.customer_id
WHERE
    o.order_id IS NULL;

这个查询使用了 WHERE o.order_id IS NULL 来筛选出所有没有下过单的客户。

七、总结与展望：数据库之路，永无止境

好了，各位老铁，今天的“数据库奇妙夜”就到这里了。希望通过今天的讲解，大家能够对 LEFT JOIN 的 WHERE 子句有更深入的理解，避免掉进坑里。

数据库的世界浩瀚无垠，还有很多有趣的知识等待我们去探索。记住，学习的道路永无止境，只有不断学习，才能成为真正的数据库高手！💪

下次“数据库奇妙夜”，咱们再见！ 👋

发表回复 取消回复

发表回复取消回复