[SQL] 多表查询之：LEFT JOIN ON 语句中，使用ON与WHERE的区别

结论

仅针对查询结果的比较：

一、INNER JOIN ON条件A AND条件B 等价于 INNER JOIN ON条件A WHERE条件B

二、LEFT JOIN ON条件A WHERE条件B 不等于 LEFT JOIN ON条件A ON条件B

三、LEFT JOIN ON条件A WHERE条件B 等价于 LEFT JOIN ON条件A AND条件B WHERE id IS NOT NULL

• 注：此处id为字段名

建议一：

INNER JOIN中，多条件使用 ON或 WHERE的查询结果没有区别（本质有区别），但建议使用INNER JOIN ON条件A AND条件B，因为 SQL 语句执行顺序为 FROM -> ON -> JOIN -> WHERE，会优先处理 ON子句的条件，减少连表的数据量。

建议二：

LEFT JOIN会以左表为主表，通过 ON子句的条件进行多表连接，生成临时表，无论 ON 的条件是否为真都会返回左表的全部记录，而 WHERE是在（连表后）临时表中进行的筛选，过滤掉所有条件不为真的记录。因此在写LEFT JOIN时，选择LEFT JOIN ON条件A WHERE条件B，将筛选条件放在 WHERE子句才能得到目标筛选记录。

---

具体分析详见下文

---

示例

Employee 表 - SQL架构

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。

本次示例仅验证 ON 与 WHERE 查询后的记录差异，以SELECT *进行查询，如需解题语句，点击此处。

---

验证一

INNER JOIN ON条件A AND条件B 等价于 INNER JOIN ON条件A WHERE条件B。

SQL语句1. INNER JOIN ON条件A AND条件B

SELECT
	* 
FROM
	Employee AS e1
	INNER JOIN Employee e2 ON e1.ManagerId = e2.Id 
	AND e1.Salary > e2.Salary;

结果1：

+------+------+--------+-----------+------+------+--------+-----------+
| Id   | Name | Salary | ManagerId | Id   | Name | Salary | ManagerId |
+------+------+--------+-----------+------+------+--------+-----------+
|    1 | Joe  |  70000 |         3 |    3 | Sam  |  60000 |      NULL |
+------+------+--------+-----------+------+------+--------+-----------+

---

SQL语句2. INNER JOIN ON条件A WHERE条件B

SELECT
	*
FROM
	Employee AS e1
	INNER JOIN Employee e2 ON e1.ManagerId = e2.Id 
WHERE
	e1.Salary > e2.Salary;

结果2：

+------+------+--------+-----------+------+------+--------+-----------+
| Id   | Name | Salary | ManagerId | Id   | Name | Salary | ManagerId |
+------+------+--------+-----------+------+------+--------+-----------+
|    1 | Joe  |  70000 |         3 |    3 | Sam  |  60000 |      NULL |
+------+------+--------+-----------+------+------+--------+-----------+

---

SQL语句3. INNER JOIN WHERE条件A AND条件B

SELECT
	* 
FROM
	Employee AS e1
	INNER JOIN Employee e2 
WHERE
	e1.ManagerId = e2.Id 
	AND e1.Salary > e2.Salary;

结果3：

+------+------+--------+-----------+------+------+--------+-----------+
| Id   | Name | Salary | ManagerId | Id   | Name | Salary | ManagerId |
+------+------+--------+-----------+------+------+--------+-----------+
|    1 | Joe  |  70000 |         3 |    3 | Sam  |  60000 |      NULL |
+------+------+--------+-----------+------+------+--------+-----------+

[ 验证一 ] 结论：

INNER JOIN 中，多条件使用 ON 或 WHERE 的查询结果没有区别，但建议使用INNER JOIN ON条件A AND条件B，因为 SQL 语句执行顺序为 FROM -> ON -> JOIN -> WHERE，会优先处理 ON子句的条件，减少连表的数据量。

---

验证二

LEFT JOIN ON条件A WHERE条件B 不等于 LEFT JOIN ON条件A ON条件B。

SQL语句1. LEFT JOIN ON条件A WHERE条件B

SELECT
	*
FROM
	Employee AS e1
	LEFT JOIN Employee e2 ON e1.ManagerId = e2.Id 
WHERE
	e1.Salary > e2.Salary;

结果1：

+------+------+--------+-----------+------+------+--------+-----------+
| Id   | Name | Salary | ManagerId | Id   | Name | Salary | ManagerId |
+------+------+--------+-----------+------+------+--------+-----------+
|    1 | Joe  |  70000 |         3 |    3 | Sam  |  60000 |      NULL |
+------+------+--------+-----------+------+------+--------+-----------+

结果1分析：

先执行 ON 子句条件进行多表连接，形成下表：

+------+-------+--------+-----------+------+------+--------+-----------+
| Id   | Name  | Salary | ManagerId | Id   | Name | Salary | ManagerId |
+------+-------+--------+-----------+------+------+--------+-----------+
|    1 | Joe   |  70000 |         3 |    3 | Sam  |  60000 |      NULL |
|    2 | Henry |  80000 |         4 |    4 | Max  |  90000 |      NULL |
|    3 | Sam   |  60000 |      NULL | NULL | NULL |   NULL |      NULL |
|    4 | Max   |  90000 |      NULL | NULL | NULL |   NULL |      NULL |
+------+-------+--------+-----------+------+------+--------+-----------+

再进行 WHERE 条件筛选，得到符合条件的一行结果，如下：

+------+------+--------+-----------+------+------+--------+-----------+
| Id   | Name | Salary | ManagerId | Id   | Name | Salary | ManagerId |
+------+------+--------+-----------+------+------+--------+-----------+
|    1 | Joe  |  70000 |         3 |    3 | Sam  |  60000 |      NULL |
+------+------+--------+-----------+------+------+--------+-----------+

---

SQL语句2. LEFT JOIN ON条件A AND条件B

SELECT
	* 
FROM
	Employee AS e1
	LEFT JOIN Employee e2 ON e1.ManagerId = e2.Id 
	AND e1.Salary > e2.Salary;

结果2：

+------+-------+--------+-----------+------+------+--------+-----------+
| Id   | Name  | Salary | ManagerId | Id   | Name | Salary | ManagerId |
+------+-------+--------+-----------+------+------+--------+-----------+
|    1 | Joe   |  70000 |         3 |    3 | Sam  |  60000 |      NULL |
|    2 | Henry |  80000 |         4 | NULL | NULL |   NULL |      NULL |
|    3 | Sam   |  60000 |      NULL | NULL | NULL |   NULL |      NULL |
|    4 | Max   |  90000 |      NULL | NULL | NULL |   NULL |      NULL |
+------+-------+--------+-----------+------+------+--------+-----------+

结果2分析：

1. 执行 ON 子句的条件A + 条件B，进行 LEFT JOIN 连表，生成临时表。

2. 无论 ON 子句的条件是否为真，都要返回 LEFT JOIN 左侧的主表的全部记录（4行记录）。

3. 对于不满足 ON 条件的记录（附表/右表中）会全部显示为 NULL （如第 2 - 4 行）。

---

验证三

LEFT JOIN ON条件A WHERE条件B 等价于 LEFT JOIN ON条件A AND条件B WHERE id IS NOT NULL

在验证二的基础上进行查询：LEFT JOIN ON条件A AND条件B 的结果为：

+------+-------+--------+-----------+------+------+--------+-----------+
| Id   | Name  | Salary | ManagerId | Id   | Name | Salary | ManagerId |
+------+-------+--------+-----------+------+------+--------+-----------+
|    1 | Joe   |  70000 |         3 |    3 | Sam  |  60000 |      NULL |
|    2 | Henry |  80000 |         4 | NULL | NULL |   NULL |      NULL |
|    3 | Sam   |  60000 |      NULL | NULL | NULL |   NULL |      NULL |
|    4 | Max   |  90000 |      NULL | NULL | NULL |   NULL |      NULL |
+------+-------+--------+-----------+------+------+--------+-----------+

上表中若想得到最终结果，需筛选（附表/右表）中不为空的记录，可以用 NOT NULL 实现：LEFT JOIN ON条件A AND条件B WHERE id IS NOT NULL

SQL语句

SELECT
	* 
FROM
	Employee AS e1
	LEFT JOIN Employee e2 ON e1.ManagerId = e2.Id 
	AND e1.Salary > e2.Salary 
WHERE
	e2.Id IS NOT NULL;

结果：

+------+------+--------+-----------+------+------+--------+-----------+
| Id   | Name | Salary | ManagerId | Id   | Name | Salary | ManagerId |
+------+------+--------+-----------+------+------+--------+-----------+
|    1 | Joe  |  70000 |         3 |    3 | Sam  |  60000 |      NULL |
+------+------+--------+-----------+------+------+--------+-----------+

[ 验证二 + 三 ] 结论：

1. LEFT JOIN会以左表为主表，通过 ON子句的条件进行多表连接，生成临时表，无论 ON的条件是否为真都会返回左表的全部记录，而 WHERE是在（连表后）临时表中进行的筛选，过滤掉所有条件不为真的记录。

2. 不符合 ON条件的记录会显示为 NULL （连接后的右表\附表部分的记录为 NULL）。

3. LEFT JOIN ON条件A AND条件B WHERE id IS NOT NULL可以得到目标记录，与 LEFT JOIN ON条件A WHERE条件B 相同的查询结果（结果相同 本质不同）。

4. 在写LEFT JOIN时，要选择LEFT JOIN ON条件A WHERE条件B，要将筛选条件放在 WHERE子句才能得到目标筛选记录。

---

错误写法：

注意：LEFT JOIN 必须含有 ON 子句，缺少 ON 会报错。

SELECT
	* 
FROM
	Employee AS e1
	LEFT JOIN Employee e2 
WHERE
	e1.ManagerId = e2.Id 
	AND e1.Salary > e2.Salary;

报错结果：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE
e1.ManagerId = e2.Id