关于外连接和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