MYSQL LEFT JOIN操作中 ON与WHERE放置条件的区别

测试
表1：table2

id No
1 n1
2 n2
3 n3
表2：table2

No name
n1 aaa
n2 bbb
n3 ccc

首先创建下面的两个表

CREATE TABLE `t2` (
  `no` varchar(11) NOT NULL,
  `name` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t1` (
  `id` tinyint(4) NOT NULL,
  `no` varchar(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select * from t1;
+----+----+
| id | no |
+----+----+
|  1 | n1 |
|  2 | n2 |
|  3 | n3 |
|  4 | n4 |
+----+----+
4 rows in set

mysql> select * from t2;
+----+------+
| no | name |
+----+------+
| n1 | aaa  |
| n2 | bbb  |
| n3 | ccc  |
+----+------+
3 rows in set

mysql> 

现在我们来对上面的数据进行操作；

1、测试场景一 t1表左连接t2表 右连接 inner 连接t2表

mysql> select * from t1 join t2 on t1.no = t2.no;
+----+----+----+------+
| id | no | no | name |
+----+----+----+------+
|  1 | n1 | n1 | aaa  |
|  2 | n2 | n2 | bbb  |
|  3 | n3 | n3 | ccc  |
+----+----+----+------+
3 rows in set

mysql> select * from t1 left 
join t2 on t1.no = t2.no;
+----+----+------+------+
| id | no | no   | name |
+----+----+------+------+
|  1 | n1 | n1   | aaa  |
|  2 | n2 | n2   | bbb  |
|  3 | n3 | n3   | ccc  |
|  4 | n4 | NULL | NULL |
+----+----+------+------+
4 rows in set
mysql> select * from t1 right join t2 on t1.no = t2.no;
+----+----+----+------+
| id | no | no | name |
+----+----+----+------+
|  1 | n1 | n1 | aaa  |
|  2 | n2 | n2 | bbb  |
|  3 | n3 | n3 | ccc  |
+----+----+----+------+
3 rows in set

mysql> 

 

测试场景2：在left join 中 使用on 和where 给t2表添加限制条件，二者的区别为

mysql> select * from t1 left
 join t2 on t1.no = t2.no and t2.name = 'aaa';
+----+----+------+------+
| id | no | no   | name |
+----+----+------+------+
|  1 | n1 | n1   | aaa  |
|  2 | n2 | NULL | NULL |
|  3 | n3 | NULL | NULL |
|  4 | n4 | NULL | NULL |
+----+----+------+------+
4 rows in set

 

mysql> select * from t1 left join t2 on t1.no = t2.no where t2.name = 'aaa'
;
+----+----+----+------+
| id | no | no | name |
+----+----+----+------+
|  1 | n1 | n1 | aaa  |
+----+----+----+------+
1 row in set

 

从测试2我们可以看出：

结论：

首先明确两个概念：

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在left join下，两者的区别：

on是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表 (table_name1) 的行。
where则是在生成临时表之后使用的条件，此时已经不管是否使用了left join了，只要条件不为真的行，全部过滤掉。

 

 

如果使用inner join 二者查询的结果是一致的，这里要注意

mysql> select * from t1 
 join t2 on t1.no = t2.no where t2.name = 'aaa';
+----+----+----+------+
| id | no | no | name |
+----+----+----+------+
|  1 | n1 | n1 | aaa  |
+----+----+----+------+
1 row in set
mysql> select * from t1  join t2 on t1.no = t2.no and
 t2.name = 'aaa';
+----+----+----+------+
| id | no | no | name |
+----+----+----+------+
|  1 | n1 | n1 | aaa  |
+----+----+----+------+
1 row in set

 

user表:

id | name
---------
1 | libk
2 | zyfon
3 | daodao

user_action表:

user_id | action
---------------
1 | jump
1 | kick
1 | jump
2 | run
4 | swim

sql:
select id, name, action from user as u
left join user_action a on u.id = a.user_id

result:
id | name | action
--------------------------------
1 | libk | jump ①
1 | libk | kick ②
1 | libk | jump ③
2 | zyfon | run ④
3 | daodao | null ⑤

分析：
注意到user_action中还有一个user_id=4, action=swim的纪录，但是没有在结果中出现，
而user表中的id=3, name=daodao的用户在user_action中没有相应的纪录，但是却出现在了结果集中
因为现在是left join，所有的工作以left为准.
结果1，2，3，4都是既在左表又在右表的纪录，5是只在左表，不在右表的纪录

结论：
我们可以想象left join 是这样工作的
从左表读出一条，选出所有与on匹配的右表纪录(n条)进行连接，形成n条纪录(包括重复的行，如：结果1和结果3)，
如果右边没有与on条件匹配的表，那连接的字段都是null.
然后继续读下一条。

引申：
我们可以用右表没有on匹配则显示null的规律, 来找出所有在左表，不在右表的纪录， 注意用来判断的那列必须声明为not null的。
如：
sql:
select id, name, action from user as u
left join user_action a on u.id = a.user_id
where a.user_id is NULL
(注意:1.列值为null应该用is null 而不能用=NULL
2.这里a.user_id 列必须声明为 NOT NULL 的)
result:
id | name | action
--------------------------
3 | daodao | NULL

 

总结：on 是表的连接条件，where是在表形成之后对表的过滤条件