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で自己結合することになる。

CREATE TABLE EMPLOYEE (
   ID INTEGER NOT NULL PRIMARY KEY,
   NAME VARCHAR2(60) NOT NULL,
   BIRTHDAY DATE NOT NULL,
   BOSS_ID INTEGER,
   FOREIGN KEY (BOSS_ID) REFERENCES EMPLOYEE (ID) ON DELETE SET NULL
)
検証用テーブルデータの作成_1

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

INSERT INTO EMPLOYEE VALUES (1, 'テスト　上司１', '1965-05-15', NULL);
INSERT INTO EMPLOYEE VALUES (2, 'テスト　上司２', '1972-03-24', NULL);

INSERT INTO EMPLOYEE VALUES (11, 'テスト　部下１', '1984-09-30', 1);
INSERT INTO EMPLOYEE VALUES (12, 'テスト　部下２', '1991-01-06', 1);
INSERT INTO EMPLOYEE VALUES (13, 'テスト　部下３', '1982-11-10', 2);
INSERT INTO EMPLOYEE VALUES (14, 'テスト　部下４', '1988-06-25', 2);
INSERT INTO EMPLOYEE VALUES (15, 'テスト　部下５', '1980-02-15', 1);

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

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

SELECT * FROM EMPLOYEE

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



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

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

SELECT * 
FROM EMPLOYEE
WHERE BOSS_ID IN ( 
  SELECT ID 
  FROM EMPLOYEE 
  WHERE BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD') 
)
ORDER BY ID ASC
IN句による自己結合するSQLの作成_1

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

SELECT ID 
FROM EMPLOYEE 
WHERE BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD')
IN句による自己結合するSQLの作成_2
SELECT * 
FROM EMPLOYEE
WHERE BOSS_ID IN ( 1 )
ORDER BY ID ASC
IN句による自己結合するSQLの作成_3



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

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

SELECT emp.* 
FROM EMPLOYEE emp
WHERE EXISTS (
  SELECT 1
  FROM EMPLOYEE sub
  WHERE sub.ID = emp.BOSS_ID
  AND sub.BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD') 
)
ORDER BY emp.ID ASC
EXISTS句による自己結合するSQLの作成_1

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



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

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

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

SELECT emp.* 
FROM EMPLOYEE emp
WHERE EXISTS (
  SELECT 1
  FROM EMPLOYEE
  WHERE ID = emp.BOSS_ID
  AND BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD') 
)
ORDER BY emp.ID ASC
エイリアスを付与しなかった場合の実行結果_1

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

SELECT * 
FROM EMPLOYEE
WHERE EXISTS (
  SELECT 1
  FROM EMPLOYEE sub
  WHERE sub.ID = BOSS_ID
  AND sub.BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD') 
)
ORDER BY ID ASC
エイリアスを付与しなかった場合の実行結果_2_1

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

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

SELECT * 
FROM EMPLOYEE
WHERE EXISTS (
  SELECT 1
  FROM EMPLOYEE sub
  WHERE sub.ID = sub.BOSS_ID
  AND sub.BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD') 
)
ORDER BY ID ASC
エイリアスを付与しなかった場合の実行結果_2_2

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

INSERT INTO EMPLOYEE VALUES (3, 'テスト　上司３', '1965-05-15', 3);
COMMIT;
エイリアスを付与しなかった場合の実行結果_2_3
SELECT * 
FROM EMPLOYEE
WHERE EXISTS (
  SELECT 1
  FROM EMPLOYEE sub
  WHERE sub.ID = BOSS_ID
  AND sub.BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD') 
)
ORDER BY ID ASC
エイリアスを付与しなかった場合の実行結果_2_4

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

要点まとめ

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