【MySQL】LEFT JOIN表时条件的位置对查询结果的影响

前言

我们知道，JOIN表的查询条件可以放在ON语句处，也可以放在 WHERE 语句处。

用 t2.status = 1 这条件举例：

方式1：放在 WHERE 语句处

SELECT t1.* FROM table1 t1
LEFT JOIN table2 AS t2 ON t2.k1 = t1.k2
WHERE t2.`status` = 1

方式2：放在 ON 语句处

SELECT t1.* FROM table1 t1
LEFT JOIN table2 AS t2 ON (t2.k1 = t1.k2 AND t2.`status` = 1)

平时做开发我基本都是用 方式1，而 方式2 就较少使用，可能会有人像我一样，觉得这两种方式的查询结果是一样的，但其实不是的。

测试

建立测试表

用户表：

CREATE TABLE `users` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `name` varchar(255) NOT NULL COMMENT '昵称',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

文章表：

CREATE TABLE `archives` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '文章ID',
  `title` varchar(255) NOT NULL COMMENT '文章标题',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态，1=发布，0=未发布',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

用户表的数据：

+-----+-------+
| uid | name  |
+-----+-------+
|   1 | tim   |
|   2 | rocky |
+-----+-------+

文章表的数据：

+----+-------+---------+--------+
| id | title | user_id | status |
+----+-------+---------+--------+
|  1 | Hello |       1 |      1 |
|  5 | World |       1 |      1 |
+----+-------+---------+--------+

可以看出，uid=1有两篇文章，uid=2没有文章。

查询

假设我们现在要查出所有的用户，以及每个用户下已发布的文章，无论用户有没有文章，所有用户都要出现在查询结果里。

WHERE条件查询：

mysql> SELECT u.*,a.id FROM users u
    -> LEFT JOIN archives AS a ON a.user_id = u.uid
    -> WHERE a.`status` = 1;
+-----+------+------+
| uid | name | id   |
+-----+------+------+
|   1 | tim  |    1 |
|   1 | tim  |    5 |
+-----+------+------+

ON条件查询：

mysql> SELECT u.*,a.id FROM users u
    -> LEFT JOIN archives AS a ON (a.user_id = u.uid AND a.`status` = 1);
+-----+-------+------+
| uid | name  | id   |
+-----+-------+------+
|   1 | tim   |    1 |
|   1 | tim   |    5 |
|   2 | rocky | NULL |
+-----+-------+------+

从这里可以看出，两种查询方式的结果是不一样的，ON条件查询才符合我们的要求。

接着，我们改写下 WHERE 查询，加多一个 OR a.status IS NULL 的条件，以应对用户没有文章时的情形，看有没有变化：

mysql> SELECT u.*,a.id FROM users u
    -> LEFT JOIN archives AS a ON a.user_id = u.uid
    -> WHERE a.`status` = 1 OR a.`status` IS NULL;
+-----+-------+------+
| uid | name  | id   |
+-----+-------+------+
|   1 | tim   |    1 |
|   1 | tim   |    5 |
|   2 | rocky | NULL |
+-----+-------+------+

OK，此时已经符合我们的查询要求了！

但是…

如果往archives表里新增一条 [user_id=2，status=0] 的数据：

+----+-------+---------+--------+
| id | title | user_id | status |
+----+-------+---------+--------+
|  1 | Hello |       1 |      1 |
|  5 | World |       1 |      1 |
|  8 | Apple |       2 |      0 |
+----+-------+---------+--------+

此时再执行WHERE查询：

mysql> SELECT u.*,a.id FROM users u
    -> LEFT JOIN archives AS a ON a.user_id = u.uid
    -> WHERE a.`status` = 1 OR a.`status` IS NULL;
+-----+------+------+
| uid | name | id   |
+-----+------+------+
|   1 | tim  |    1 |
|   1 | tim  |    5 |
+-----+------+------+

查询结果又不符合我们的要求了，这是因为，uid=2 的文章JOIN进来后，因status=0不符合 status = 1 OR status IS NULL 这条件被过滤了。

而 ON查询 仍然符合查询要求。

结论

从测试结果来看，两种查询方式的行为是不一样的：

• 条件放在 WHERE 语句时，外表数据行是 JOIN 进来后再过滤的，如果被过滤了，则连同主表的数据行都会一并从结果集里移除
• 条件放在 ON 语句时，外表数据行是过滤完后再 JOIN 进来的，如果被过滤，JOIN进来的就是NULL，主表的数据行不会受影响