MENU
DB

自己結合を含むSQLのIN句とEXISTS句を試してみた

同じテーブル同士を結合することを「自己結合」という。自己結合を含むSQLでは、結合する2テーブルの区別を付かないことによる不具合が発生し得るため、テーブルにエイリアス(一時的な名前)を付ける必要がある。

今回は、Oracleデータベース上で、自己結合を含むSQLのIN句とEXISTS句を試してみたので、その内容を共有する。

前提条件

Oracle XEのインストールが完了していること。

やってみたこと

  1. 検証用テーブルデータの作成
  2. IN句による自己結合するSQLの作成
  3. EXISTS句による自己結合するSQLの作成
  4. エイリアスを付与しなかった場合の実行結果

検証用テーブルデータの作成

今回は、検証用テーブルデータとしてEMPLOYEEテーブルを作成する。その手順は、以下の通り。

1) EMPLOYEEテーブルを作成する。このテーブルは、IDとBOSS_IDで自己結合することになる。

検証用テーブルデータの作成_1

2) EMPLOYEEテーブルにデータを追加する。

検証用テーブルデータの作成_2

3) 作成後のEMPLOYEEテーブルのデータは、以下の通り。

検証用テーブルデータの作成_3



IN句による自己結合するSQLの作成

先ほど作成したEMPLOYEEテーブルから、生年月日が1965年5月15日の上司をもつデータを取得するSQLを、IN句を用いて実行する。そのSQLの内容は、以下の通り。

IN句による自己結合するSQLの作成_1

上記の実行結果は、以下のSQLの組み合わせのように、生年月日が1965年5月15日の上司のID(BOSS_ID)が1であるデータを抽出した結果と同じになる。

IN句による自己結合するSQLの作成_2

IN句による自己結合するSQLの作成_3



EXISTS句による自己結合するSQLの作成

生年月日が1965年5月15日の上司をもつデータを取得するSQLは、EXISTS句を用いても実行できる。そのSQLの内容は、以下の通り。

EXISTS句による自己結合するSQLの作成_1

上記のように、外側のEMPLOYEEテーブルと内側のEMPLOYEEテーブルの両方に、それぞれ別のエイリアスであるemp, subを付与することで、正常に実行でき、IN句の場合と同じ実行結果になる。



エイリアスを付与しなかった場合の実行結果

外側のEMPLOYEEテーブル(emp)と内側のEMPLOYEEテーブル(sub)のそれぞれのエイリアスを削除した場合の実行結果は以下の通りで、結合する2テーブルの区別を付かないことによる不具合が発生する場合がある。

1) 内側のEMPLOYEEテーブルのエイリアス「sub」を削除した場合は、以下のように、正常に実行できることが確認できる。

エイリアスを付与しなかった場合の実行結果_1

2) 外側のEMPLOYEEテーブルのエイリアス「emp」を削除した場合は、以下のように、実行結果がおかしくなることが確認できる。

エイリアスを付与しなかった場合の実行結果_2_1

これは、WHERE句の「sub.ID = BOSS_ID」であるデータを取得する際のBOSS_IDが、外側のEMPLOYEEテーブルでなく、subエイリアスがついた内側のテーブルのBOSS_IDを見に行ってしまうために発生する。

以下のように、WHERE句の「sub.ID = BOSS_ID」を「sub.ID = sub.BOSS_ID」と書き換えた場合と同じ実行結果になる。

エイリアスを付与しなかった場合の実行結果_2_2

また、以下のID=BOSS_IDで、誕生日が1965/5/15のデータを追加すると、EXISTS句の中身がTRUEになるため、全件が検索されることが確認できる。

エイリアスを付与しなかった場合の実行結果_2_3

エイリアスを付与しなかった場合の実行結果_2_4

このように、自己結合を含むSQLでは、結合する2テーブルの区別を付かないことによる不具合が発生することがあるため、テーブルにエイリアス(一時的な名前)を必ず付与するようにした方が良いと思う。

要点まとめ

  • 同じテーブル同士を結合することを「自己結合」という。
  • 自己結合を含むSQLでは、結合する2テーブルの区別を付かないことによる不具合が発生し得るため、テーブルにエイリアス(一時的な名前)を付ける必要がある。