数据库在连接两张或者多张表来返回记录时,都会生成一个中间的临时表,然后再将这张临时表返回给用户。
在使用left join时,on和where条件的区别如下:
1.on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2.where条件是在临时表生成好后,再对临时表进行过滤的条件,这时,已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就会全部过滤掉。
eg:
表1:
select * from tab1;
id | size |
1 | 10 |
2 | 20 |
3
4
|
30
11
|
表2:
select * from tab2;
size | name |
10 | aaa |
20 | bbb |
20 | ccc |
分析下边两条sql
select * from tab1 left join tab2 on (tab1.size=tab.size) where tab2.name="aaa";
第一条sql的过程:
中间表on条件:
tab1.size=tab2.size
tab1.id | tab1.size | tab2.size | tab2.name |
1 | 10 | 10 | AAA |
2 | 20 | 20 | BBB |
2 | 20 | 20 | CCC |
3 | 30 | null | null |
再对中间表过滤where条件tab2.name=AAA
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
select * from tab1 left join tab2 on (tab1.size =tab2.size and tab2.name="aaa")
第二条sql执行过程:
中间表on条件(条件不为真也会返回左边表中的记录)
tab1.id | tab1.size | tab2.id | tab2.name |
1 | 10 | 10 | AAA |
2 | 20 | null | null |
3 | 30 | null | null |
以上结果的关键原因就是left join,right join ,full join 的特性,不管on上的条件是否为真都会返回left或者right表中的记录,full则具有left和right的特性的并集,而inner join没有这个特性,则条件放在on中和where中,返回的结果集是相同的。
总结:
1.对于left join,不管on后面跟什么条件,左表的数据全部查出来,因此想要过滤需要把条件放到where后面
2.对于inner join,满足on后面的条件表的数据才能查出,可以起到过滤作用,也可以把条件放到where后面。