oracle 中的左连接

在数据仓库项目的ETL开发中，左关联是常常用到的。但是我们可能对于左关联缺少深入学习，有时会带来我们没有预料到的错误。下面我们就来讨论一下左连接的问题（下面的讨论以Oracle为基础，但是对于其他数据库应该是大同小异，可以参考）：

 

1 左关联的两种基本写法

select * from a,b where a.id=b.id(+)

select * from a left outer join b on a.id=b.id

 

2 左关联过滤条件的两种写法

select * from a,b where a.id=b.id(+) and a.name=’C’ 该种左连接写法下只有一种过滤条件写法

select * from a left outer join b on a.id=b.id and a.name=’C’

select * from a left outer join b on a.id=b.id where a.name=’C’

同时过滤条件可能只是主表有过滤条件，也可能只是从表有过滤条件，也可能两者皆有过滤条件。

 

3 左连接还可以是多个表左连接

select * from a,b,c where a.id=b.id(+) and a.code =c.cd(+)

还可能a关联b，b再关联c

select * from a left outer join b on a.id=b.id

left outer join c on b.code=c.cd(+)

 

前面就算是铺垫了，可以看到左关联变化还不少，一组合起来，变化就更多，那么最终一个复杂的左关联下来也许就跟你预期的结果相去甚远。

 

过滤条件On和Where的区别。Where是先连接再过滤，对于On则是先关联，再取从表中满足过滤条件的记录，如果过滤条件在从表，可以看作是先过滤从表再连接（执行计划可以看出来），如果过滤条件在主表，并不是先过滤，再连接（要达到该效果，需要用子查询或者把过滤条件写在where中，对于主表的过滤来说，先过滤再连接和先连接再过滤一个意思），从查询计划可以看出，是先内连接，再取满足过滤条件的记录，再和主表左关联。对于用+表示的左连接，没有On和Where的区别，它默认的就是先连接再过滤。

 

对于多表连接时要注意，考虑以下情况：

select * from a left outer join b on a.id=b.id

left outer join c on a.code=c.cd(+)

and b.loc=’SH’

从执行计划看，是先内连接a和c，然后取b的过滤条件，形成一个视图，然后a，b和视图三者左关联。一般不要这么些，正常情况写法如下：

select * from a left outer join b on a.id=b.id and b.loc=’SH’

left outer join a on a.no=d.no(+)

过滤b，再关联。

 

一定要记住以下三点，在写SQL时要用这三点好好分析你的SQL：

1 On与Where的区别

2 ‘+’默认为先连接再过滤

3 多表连接过滤条件的位置

 

保证SQL正确的简单方法：

1 一定要记得看执行计划，看到底是怎么执行的。

2  测试

虽然变化这么多，我们可以通过简单的测试来验证你的SQL。把SQL的结果集的记录数和你预想的记录数比较一下就可以了。预想的记录数是比较简单的，谁是主表，有哪些过滤条件，这两个问题回答了就出来了。

 

其实搞复杂了，如果从实际中的运用来说，我们无非是想过滤主表或从表。首先排除用+，统一用left outer join更清晰。如果要对从表进行先过滤后连接，就把过滤条件写在on后面；如果需要对主表连接后过滤（过滤后连接一个意思，统一就是对主表过滤），则把过滤条件写在where；如果只是想对主表进行过滤，可以用+，因为对于从表过滤，我们一般的意图是想先过滤再连接，而+是统一先连接再过滤。