在 SQL 查询中，LEFT JOIN 是一种常用的连接类型，用于从左表（主表）中返回所有记录，以及右表（连接表）中满足连接条件的记录。当 LEFT JOIN 与 ON 和 WHERE 子句结合使用时，其行为和结果可能会有显著不同。本文将深入探讨这种差异，并通过实例代码进行详细解析。
1. LEFT JOIN 与 ON
ON 子句用于指定 LEFT JOIN 的连接条件。当使用 ON 子句时，即使在右表中没有匹配的记录，左表的所有记录也会被返回，右表的字段在这种情况下会填充 NULL。
示例代码
假设我们有两个表：employees（员工表）和 departments（部门表）。
-- 创建示例表
CREATE TABLE employees (
id INT,
name VARCHAR(100),
department_id INT
);
CREATE TABLE departments (
id INT,
department_name VARCHAR(100)
);
-- 插入示例数据
INSERT INTO employees (id, name, department_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', NULL);
INSERT INTO departments (id, department_name) VALUES
(1, 'HR'),
(2, 'Engineering');现在我们想要查询所有员工及其对应的部门名称，即使某些员工没有分配部门。
-- 使用 LEFT JOIN 和 ON 子句
SELECT
e.id,
e.name,
d.department_name
FROM
employees e
LEFT JOIN
departments d
ON
e.department_id = d.id;输出结果：
id | name | department_name |
1 | Alice | HR |
2 | Bob | Engineering |
3 | Charlie | NULL |
2. LEFT JOIN 与 WHERE
WHERE 子句用于过滤查询结果。当在 LEFT JOIN 后使用 WHERE 子句时，它会过滤掉所有不满足条件的记录，包括因为 LEFT JOIN 而产生的 NULL 值。
示例代码
如果我们错误地将连接条件放在 WHERE 子句中：
-- 使用 LEFT JOIN 和错误的 WHERE 子句
SELECT
e.id,
e.name,
d.department_name
FROM
employees e
LEFT JOIN
departments d
ON
1=1 -- 这里故意使用一个总是为真的条件
WHERE
e.department_id = d.id;输出结果：
id | name | department_name |
1 | Alice | HR |
2 | Bob | Engineering |
注意到 Charlie 被排除了，因为 WHERE 子句过滤掉了 department_id 不匹配的记录，包括那些因为 LEFT JOIN 本应返回 NULL 的记录。
3. 正确使用 ON 和 WHERE
场景一：过滤连接后的结果
如果我们想要查询所有员工及其部门名称，但只显示那些部门名称不为空的记录，可以这样做：
-- 使用 LEFT JOIN 和正确的 WHERE 子句
SELECT
e.id,
e.name,
d.department_name
FROM
employees e
LEFT JOIN
departments d
ON
e.department_id = d.id
WHERE
d.department_name IS NOT NULL;输出结果：
id | name | department_name |
1 | Alice | HR |
2 | Bob | Engineering |
场景二：额外的过滤条件
如果我们想要查询所有在“Engineering”部门的员工，同时保留其他员工的记录但部门名称为空，可以这样做：
-- 使用 LEFT JOIN、ON 和额外的过滤条件
SELECT
e.id,
e.name,
d.department_name
FROM
employees e
LEFT JOIN
departments d
ON
e.department_id = d.id
AND
d.department_name = 'Engineering'; -- 这个条件放在 ON 中输出结果：
id | name | department_name |
1 | Alice | NULL |
2 | Bob | Engineering |
3 | Charlie | NULL |
注意到 Alice 和 Charlie 仍然保留在结果中，但他们的部门名称显示为 NULL，因为他们不属于“Engineering”部门。
结论
在 LEFT JOIN 中，ON 和 WHERE 子句的使用有显著区别。ON 子句用于指定连接条件，而 WHERE 子句用于过滤结果。将连接条件错误地放在 WHERE 子句中会导致 LEFT JOIN 的行为类似于 INNER JOIN，从而失去左表中的一些记录。正确理解和使用 ON 和 WHERE 子句对于获得预期的查询结果至关重要。
