關於外連線和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