mysql left join之后的行数有可能与左表的行数不同

首先，谈一下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 条件里。