首先,谈一下left join时,放在 on 后面的条件与放在 where 后面的条件有什么不同:
on是在生成连接表的起作用,where是生成连接表之后对连接表再进行过滤:
当使用left join时,无论on的条件是否满足,都会返回左表的所有记录,对于满足的条件的记录,两个表对应的记录会连接起来,对于不满足条件的记录,那右表字段全部是null;
当使用right join时,类似,只不过是全部返回右表的所有记录
案例实践:
假设有两张表:
表1:tab2
id | size |
1 | 10 |
2 | 20 |
3 | 30 |
表2:tab2
size | name |
10 | AAA |
20 | BBB |
30 | CCC |
两条SQL:
1、select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'
2、select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=‘AAA’)
第一条SQL的过程:
1、中间表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) |
2、再对中间表过滤where条件:tab2.name=‘AAA’
tab1.id | tab1.size | tab2.size | tab2.name |
1 | 10 | 10 | AAA |
第二条SQL的过程:
1、中间表on条件:tab1.size = tab2.size and tab2.name = ‘AAA’
tab1.id | tab1.size | tab2.size | tab2.name |
1 | 10 | 10 | AAA |
2 | 20 | (null) | (null) |
3 | 30 | (null) | (null) |
但是需要说明一点,left join之后的结果行数,有可能大于左表行数:
实验如下:
SELECT * FROM test1 a LEFT JOIN test2 b ON a.number = b.number
其中test1表的数据如下:
a_id | number |
1 | 100 |
2 | 200 |
3 | 300 |
4 | 400 |
5 | 500 |
6 | 600 |
test2表的数据如下:
b_id | number |
1 | 100 |
2 | 200 |
3 | 100 |
4 | 100 |
因此 left join 后变成:
a_id | number | b_id |
1 | 100 | 1 |
2 | 200 | 2 |
1 | 100 | 3 |
1 | 100 | 4 |
3 | 300 | null |
4 | 400 | null |
5 | 500 | null |
6 | 600 | null |
test1 有6条记录, test2 有4条记录,left join 后,产生8条记录,因为这个时候 test2 有重复的 number,而 number 正好是在 on 条件里。