SQL left join 特殊情况

我们通常认为A left join B后，结果集与A表的记录条数一致

但，并不是这样

with  x as 
(
select 1 as id,90 as score from dual union all
select 2 as id,80 as score from dual union all
select 3 as id,70 as score from dual
),
y as (
select 1 as id ,'John' as name from dual union all
select 2 as id,'Adam' as name from dual 
)
select x.id,x.score,y.name from x
left join y on x.id=y.id

 

 这样的结果是我们预期的，

但如果修改一下，连接条件是id相等，那么我们尝试 让y表的id有两个相等的

with  x as 
(
select 1 as id,90 as score from dual union all
select 2 as id,80 as score from dual union all
select 3 as id,70 as score from dual
),
y as (
select 1 as id ,'John' as name from dual union all
select 2 as id,'Adam' as name from dual union all
select 2 as id,'Adam2' as name from dual union all
)
select x.id,x.score,y.name from x
left join y on x.id=y.id

 

这时的结果集就是多了一条，所以，在使用left join进行表关联时

还是很需要注意这个问题的。