Oracle语法：leftjoin查询时筛选条件写在on和where中的区别

示例

left join 和 right join 是Oracle中常用的关联查询，但在使用时如果不注意，可能会导致本来想要 left join 或 right join 的查询结果，实际上却是 inner join 效果的查询。这是什么原因呢？下面举一个示例说明一下。

假设有如下两张表：
表1：tab1

id	size
1	10
2	20
3	30

表2：tab2

size	name
10	AAA
20	BBB
20	CCC

第一条sql：

select * form tab1 left join tab2 on (tab1.size = tab2.size) where 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过滤条件：where tab2.name=’AAA’

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA

3. 可见这种写法和inner join的结果是一样的。

第二条sql：

select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA');

这条sql的执行过程是：

1. 先通过on条件中的过滤条件tab2.name='AAA'筛选出 tab2 需要去连表的临时数据

size	name
10	AAA

2. 然后通过on中的连表条件tab1.size = tab2.size将 tab1 和上面的临时表进行连表

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	(null)	(null)
3	30	(null)	(null)

3. 因为没有where条件，就此得到上面的查询结果。

结论

从上面的例子中，我们就可以看到筛选条件写在on和where中会有不同的查询结果，在使用left jion时，on和where条件的区别如下：

1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

通常我们使用 left join 查询，目的是为了得到第二条sql的查询结果，所以也就应该使用第二种方式，将被连接表的字段筛选写在 on 条件里。