Oracle　外部結合（ON句とwhere句の違い）

外部結合条件をON句で指定するのとwhere句で指定する場合の違い

外部結合時の際に指定する条件をON句に指定する場合と、where句で指定した場合の違いを説明します。参考までにですが、内部結合（INNER JOIN）の場合どちらに指定しても出力結果に違いはありません。記載する場所によって結果が異なるのは外部結合の場合だけなんです。案外バグが潜みやすいところですので動きをしっかり頭に入れておきましょう。

まずはこんな状態で２つのテーブルが存在したとします。

今回はLEFT OUTER JOINを使って説明するので、左がが基準となるテーブルとなり、２つのテーブルの結合キーは「ITEM_ID」となります。

ITEM_IDで結合した場合（ON句で結合キーであるITEM_IDのみを指定する場合）

select p.ID,p.STORE,s.ITEM_NAME,s.PRICE,s.QUANTITY
from PRICE_LIST p left outer join STOCK_MASTER s 
on p.ITEM_ID = s.ITEM_ID
order by p.ID;

外部結合なのでマスタに存在しないITEM_ID=30を持つ池袋店も一覧に出力されます。ただし、マスタから持ってくるはずのデータは全てnullになります。

ON句に条件を追加した場合

select p.ID,p.STORE,s.ITEM_NAME,s.PRICE,s.QUANTITY
from PRICE_LIST p left outer join STOCK_MASTER s 
on p.ITEM_ID = s.ITEM_ID  and s.QUANTITY > 0
order by p.ID;

在庫があるもの（QUANTITY > 0）と指定したので、在庫=0の「リンゴ」の各種値は表示されない（null）事となります。

wherre句に条件を追加した場合

select p.ID,p.STORE,s.ITEM_NAME,s.PRICE,s.QUANTITY
from PRICE_LIST p left outer join STOCK_MASTER s 
on p.ITEM_ID = s.ITEM_ID 
where s.QUANTITY > 0
order by p.ID;

where句で指定すると、「外部結合した結果に対して」条件が適用されるイメージですね。上記のようにnull表示にするのか行そのものを非表示とするのかは、その時求められる要件によって使い分けですね。個人的な好みとなりますが、where句で指定するのであれば「外部結合」ではなく「等価結合(inner join)」のほうが受け入れやすい気がしますね。

おまけ　マスタに存在しないものを表示する場合

select p.ID,p.STORE,s.ITEM_NAME,s.PRICE,s.QUANTITY
from PRICE_LIST p left outer join STOCK_MASTER s 
on p.ITEM_ID = s.ITEM_ID 
where s.ITEM_ID is null
order by p.ID;