プロダクトコードで使われているSQLに外部結合で (+) がよくつかわれているが、11g以降非推奨だと聞いたので、個人的には保守以外であまり使わないが、必要に駆られて勉強してみた。
SQL関連の書籍でおすすめの一冊はこちら！！！
SQLアンチパターン／BillKarwin／和田卓人／和田省二
外部結合とは
外部結合とは
外部結合とは2つのテーブルでそれぞれ結合の対象となるカラムを指定し、それぞれのカラムに同じ値が格納されているデータを結合して取得するものです。内部結合の場合は、一致しないデータは取得しませんでしたが、外部結合の場合は一致しない場合もデータとして取得します。
引用元 : 外部結合とは
つまりよくある以下のようなSQL
select
h.hoge1,h.hoge2,h.hoge3,f.fuga1,f.fuga2,f.fuga3
from
HOGE h,FUGA f
where
h.hoge1 = f.fuga1
これは 内部結合 であり、 h.hoge1 = f.fuga1 が一致しないもの、つまり一方にしか存在しない場合は取得できないという事になる。
他方、非推奨とされる (+) を使った 外部結合 でSQLを書くと以下のように書ける.
右外部結合
select
h.hoge1,h.hoge2,h.hoge3,f.fuga1,f.fuga2,f.fuga3
from
HOGE h,FUGA f
where
h.hoge1(+) = f.fuga1
左外部結合
select
h.hoge1,h.hoge2,h.hoge3,f.fuga1,f.fuga2,f.fuga3
from
HOGE h,FUGA f
where
h.hoge1 = f.fuga1(+)
これは (+) を付けた方のデータがなくても空行を返すという動きをします。
ビジュアルでイメージ
テーブルの形を使うと分かりやすいのでそれで書きます。
HOGE
| hoge1 | hoge2 | hoge3 |
|---|---|---|
| a | 田中 | 男 |
| b | 高橋 | 男 |
| d | 鈴木 | 女 |
| f | 山田 | 男 |
FUGA
| fuga1 | fuga2 | fuga3 |
|---|---|---|
| a | 総務 | 課長 |
| b | 総務 | 係長 |
| c | 人事 | 部長 |
| e | 人事 | 課長 |
社員情報と部署役職情報を紐づけるものであると考えてください（たとえが悪くてすみません）。
右外部結合のSQLの場合、 hoge1 がなくても返すという動きなので
| hoge1 | hoge2 | hoge3 | fuga1 | fuga2 | fuga3 |
|---|---|---|---|---|---|
| a | 田中 | 男 | a | 総務 | 課長 |
| b | 高橋 | 男 | b | 総務 | 係長 |
| c | 人事 | 部長 | |||
| e | 人事 | 課長 |
こんな感じ。
左外部結合のSQLの場合 fuga1 がなくても返すという動きなので
| hoge1 | hoge2 | hoge3 | fuga1 | fuga2 | fuga3 |
|---|---|---|---|---|---|
| a | 田中 | 男 | a | 総務 | 課長 |
| b | 高橋 | 男 | b | 総務 | 係長 |
| d | 鈴木 | 女 | |||
| f | 山田 | 男 |
こんな感じ。
推奨の書き方
では非推奨なので上記で紹介した 左外部結合 、 右外部結合 、にくわえ完全外部結合をの形3つの推奨の書き方を紹介します。
完全外部結合
| hoge1 | hoge2 | hoge3 | fuga1 | fuga2 | fuga3 |
|---|---|---|---|---|---|
| a | 田中 | 男 | a | 総務 | 課長 |
| b | 高橋 | 男 | b | 総務 | 係長 |
| c | 人事 | 部長 | |||
| d | 鈴木 | 女 | |||
| e | 人事 | 課長 | |||
| f | 山田 | 男 |
このように取得できる 完全外部結合 は以下のように記述します。
select
h.hoge1, h.hoge2, h.hoge3, f.fuga1, f.fuga2, f.fuga3
from
HOGE h full outer join FUGA f on h.hoge1 = f.fuga1
同様に
右外部結合
select
h.hoge1, h.hoge2, h.hoge3, f.fuga1, f.fuga2, f.fuga3
from
HOGE h right outer join FUGA f on h.hoge1 = f.fuga1
左外部結合
select
h.hoge1, h.hoge2, h.hoge3, f.fuga1, f.fuga2, f.fuga3
from
HOGE h left outer join FUGA f on h.hoge1 = f.fuga1
と書けます。
※ちなみに完全外部結合は (+) を両方につけても動きません。
非推奨の理由は以下に記載があります。
出典 ：Oracle® Database SQL言語リファレンス 結合
再度整理
右外部結合
非推奨
select
h.hoge1,h.hoge2,h.hoge3,f.fuga1,f.fuga2,f.fuga3
from
HOGE h,FUGA f
where
h.hoge1(+) = f.fuga1
推奨
select
h.hoge1, h.hoge2, h.hoge3, f.fuga1, f.fuga2, f.fuga3
from
HOGE h right outer join FUGA f on h.hoge1 = f.fuga1
左外部結合
非推奨
select
h.hoge1,h.hoge2,h.hoge3,f.fuga1,f.fuga2,f.fuga3
from
HOGE h,FUGA f
where
h.hoge1 = f.fuga1(+)
推奨
select
h.hoge1, h.hoge2, h.hoge3, f.fuga1, f.fuga2, f.fuga3
from
HOGE h left outer join FUGA f on h.hoge1 = f.fuga1
最後に
最近Oracle触り始めた方、エンジニア1年目みたいな方に参考になればと思います。
これからSQL書く人は (+) は絶対使わないことを推奨します。
※補足
SQLのwhere句に使う 「＝」 は数学の 「＝」 と同じで、
左辺と右辺が等価であることを意味するため、 左右可換 です。