自己結合を含むSQLのIN句とEXISTS句を試してみた｜ITエンジニアとして経験・学習したこと

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

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

前提条件

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

やってみたこと

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

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

今回は、検証用テーブルデータとして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
)

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

)

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;

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;

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

SELECT * FROM EMPLOYEE

1	SELECT * FROM EMPLOYEE

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

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

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

SELECT ID 
FROM EMPLOYEE 
WHERE BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD')

SELECT ID

FROM EMPLOYEE

WHERE BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD')

SELECT * 
FROM EMPLOYEE
WHERE BOSS_ID IN ( 1 )
ORDER BY ID ASC

SELECT *

FROM EMPLOYEE

WHERE BOSS_ID IN ( 1 )

ORDER BY ID ASC

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

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

上記のように、外側の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

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

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

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

これは、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

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

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

INSERT INTO EMPLOYEE VALUES (3, 'テスト　上司３', '1965-05-15', 3);
COMMIT;

1 2	INSERT INTO EMPLOYEE VALUES (3, 'テスト　上司３', '1965-05-15', 3); COMMIT;

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

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