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是在表形成之后对表的过滤条件

posted on 2017-12-26 15:09  luzhouxiaoshuai  阅读(711)  评论(0编辑  收藏  举报

导航