oracle表连接 where条件,关于外连接和where条件

在外连接中，where后出现的表等同于内连接，因此，如果用了where条件，就应当将left join改为inner join。以下测试验证了这点。

with tab_a as

(

select 1 id1, 11 id2 from dual union all

select 2 id1, 22 id2 from dual union all

select 3 id1, 33 id2 from dual

), tab_b as

(

select 1 id1, 11 id2 from dual union all

select 2 id1, 22 id2 from dual union all

select 4 id1, 44 id2 from dual

)

select a.*, b.*

from tab_a a

left join tab_b b on a.id1 = b.id1

where b.id1 <> 4;

结果是没有3的

ID1        ID2        ID1        ID2

---------- ---------- ---------- ----------

1         11          1         11

2         22          2         22

如果不加where条件

with tab_a as

(

select 1 id1, 11 id2 from dual union all

select 2 id1, 22 id2 from dual union all

select 3 id1, 33 id2 from dual

), tab_b as

(

select 1 id1, 11 id2 from dual union all

select 2 id1, 22 id2 from dual union all

select 4 id1, 44 id2 from dual

)

select a.*, b.*

from tab_a a

left join tab_b b on a.id1 = b.id1

--where b.id1 <> 4;

结果是包含3的

ID1        ID2        ID1        ID2

---------- ---------- ---------- ----------

1         11          1         11

2         22          2         22

3         33