MySql left join 条件中on与 Where的区别

table1 LEFT JOIN table2 ON 语句的查询结果如下图所示:

img_leftjoin(图片来源:http://www.w3schools.com/sql/img_leftjoin.gif)

语言描述就是 A LEFT JOIN B ON 语句返回的数据是由A中所有行以及与B中匹配的行。如果与B中数据没有匹配,则返回一行所有列为NULL的行。好像说的也不太明白,直接上例子吧。

mysql> create table company(id int primary key, name varchar(128));
Query OK, 0 rows affected (0.13 sec)

mysql> create table employee(id int primary key, name varchar(128), company_id int);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into company values (1, ‘A’), (2,’B’), (3, ‘C’), (4,’D’);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into employee values (1, ‘A’, 1), (2, ‘B’, 5), (3, ‘C’, 3), (4, ‘D’, 6);
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from company;
+—-+——+
| id | name |
+—-+——+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
+—-+——+
4 rows in set (0.00 sec)

mysql> select * from employee;
+—-+——+————+
| id | name | company_id |
+—-+——+————+
| 1 | A | 1 |
| 2 | B | 5 |
| 3 | C | 3 |
| 4 | D | 6 |
+—-+——+————+
4 rows in set (0.00 sec)

mysql> select * from company c left join employee e on c.id = e.company_id;
+—-+——+——+——+————+
| id | name | id | name | company_id |
+—-+——+——+——+————+
| 1 | A | 1 | A | 1 |
| 2 | B | NULL | NULL | NULL |
| 3 | C | 3 | C | 3 |
| 4 | D | NULL | NULL | NULL |
+—-+——+——+——+————+
4 rows in set (0.00 sec)

通过上面的结果可以看到,未匹配的数据则是由NULL组成的。

那么 ON 与 Where的区别是什么呢?MySql先通过ON语句中的条件去与B表进行匹配,之后再用Where语句中的条件去过滤前面所匹配出来的数据。所以说,他们之间是有先后关系的。

还是来看个例子吧,看看过滤条件分别在ON与Where中的区别。

mysql> select * from company c left join employee e on c.id = e.company_id and e.name=’A’;
+—-+——+——+——+————+
| id | name | id | name | company_id |
+—-+——+——+——+————+
| 1 | A | 1 | A | 1 |
| 2 | B | NULL | NULL | NULL |
| 3 | C | NULL | NULL | NULL |
| 4 | D | NULL | NULL | NULL |
+—-+——+——+——+————+
4 rows in set (0.00 sec)

mysql> select * from company c left join employee e on c.id = e.company_id where e.name=’A’;
+—-+——+——+——+————+
| id | name | id | name | company_id |
+—-+——+——+——+————+
| 1 | A | 1 | A | 1 |
+—-+——+——+——+————+
1 row in set (0.00 sec)
通过上面的结果应该能明白ON(先匹配)与Where(后过滤)的先后关系了。

另外,在MySql官方文档中关于LEFT JOIN还有这么一段描述:

If you use LEFT JOIN to find rows that do not exist in some table and you have the following test: _<code>col_name_ IS NULL in the WHERE part, where _col_name_ is a column that is declared as NOT NULL, MySQL stops searching for more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition.
我的理解是,当你在Where语句中使用col_name is NULL (且该列con_name被定义为NOT NULL )来查询一些数据时,如果ON有多个匹配条件时,MySql会在执行匹配条件时,只要找到一条数据就会停止匹配剩下的条件。即短路效果。有点类似于“或”语句的短路。如 A || B || C ,当A为真时,B与C就不会去比较了。

具体例子可以参照本文参考链接中的2.

 

本文参考链接:

1、http://dev.mysql.com/doc/refman/5.7/en/left-join-optimization.html

2、http://www.mysqldiary.com/mysql-left-join/