OracleにおけるEXISTSとNOT EXISTSの使い方です。
SQL Server・MySQL・PostgreSQLなどでも構文は同じですが、今回はOracleで実行しています。
EXISTSとNOT EXISTS
特定のテーブルのカラムの値が、別のテーブルのカラムに存在するかどうかを確認したい時、EXISTSとNOT EXISTSを利用します。EXISTSは存在することを、NOT EXISTSは存在しないことを確認する際に利用します。
EXISTSの使い方
まずはEXISTSの使い方です。WHERE句にEXISTSを書いて、その後にサブクエリを書きます。構文はこんな感じです。
SELECT *
FROM TABLE_A TAB_A
WHERE EXISTS (SELECT 1
FROM TABLE_B TAB_B
WHERE TAB_B.COL_1 = TAB_A.COL_1);
TABLE_AテーブルのCOL_1カラムの値を軸にして、TABLE_BテーブルのCOL_1カラムに存在するものを取得する簡単なSQLです。
では、試しに実行してみます。TAB_PRODUCT_FRUITテーブルとMST_FRUIT_COLORテーブルを用意しました。
TAB_PRODUCT_FRUITテーブルのFRUIT_COLOR_CDと、MST_FRUIT_COLORテーブルのCOLOR_CODEは紐づいています。TAB_PRODUCT_FRUITテーブルのFRUIT_COLOR_CDのうち、MST_FRUIT_COLORテーブルのCOLOR_CODE上に存在するものを取得します。
SELECT NAME
,FRUIT_COLOR_CD
FROM TAB_PRODUCT_FRUIT TAB_FRUIT
WHERE EXISTS (SELECT 1
FROM MST_FRUIT_COLOR MST
WHERE MST.COLOR_CODE = TAB_FRUIT.FRUIT_COLOR_CD);
これを実行してみます。
MST_FRUIT_COLORテーブルに色が存在するので取得できています。TAB_PRODUCT_FRUITテーブルにある洋ナシはMST_FRUIT_COLORテーブルに存在しないため、取得結果に出ていませんね。これでOKです。
NOT EXISTSの使い方
次にNOT EXISTSの使い方です。書き方はEXISTSと同じです。EXISTSの前にNOTを付けるだけです。
SELECT *
FROM TABLE_A TAB_A
WHERE NOT EXISTS (SELECT 1
FROM TABLE_B TAB_B
WHERE TAB_B.COL_1 = TAB_A.COL_1);
TABLE_AテーブルのCOL_1カラムの値を軸にして、TABLE_BテーブルのCOL_1カラムに存在しないものを取得する簡単なSQLです。
こちらも試しに実行してみます。先ほど使用したTAB_PRODUCT_FRUITテーブルとMST_FRUIT_COLORテーブルを使用します。
SELECT NAME
,FRUIT_COLOR_CD
FROM TAB_PRODUCT_FRUIT TAB_FRUIT
WHERE NOT EXISTS (SELECT 1
FROM MST_FRUIT_COLOR MST
WHERE MST.COLOR_CODE = TAB_FRUIT.FRUIT_COLOR_CD);
これを実行してみましょう。
TAB_PRODUCT_FRUITテーブルの洋ナシのFRUIT_COLOR_CD=05はMST_FRUIT_COLORテーブルに存在しないため、「存在しませんよ」という洋ナシだけ取得できました。
サブクエリのWHERE句に条件を付ける
サブクエリのWHERE句に条件を付けることも可能です。例えば、TAB_PRODUCT_FRUITテーブルの値で、
- MST_FRUIT_COLORテーブルに存在する
- MST_FRUIT_COLORテーブルのCOLOR_CODE が’03’より小さい
という条件で取得するとします。SQLはこんな感じ。
SELECT NAME
,FRUIT_COLOR_CD
FROM TAB_PRODUCT_FRUIT TAB_FRUIT
WHERE EXISTS (SELECT 1
FROM MST_FRUIT_COLOR MST
WHERE MST.COLOR_CODE = TAB_FRUIT.FRUIT_COLOR_CD
AND MST.COLOR_CODE <= '03');
サブクエリ側にAND句を加えてます。これを実行してみましょう。
はい、サブクエリ側に条件を追加して取得できましたね。
EXISTS・NOT EXISTSのSELECT句について
EXISTS・NOT EXISTSのサブクエリのSELECT句に何を書くかですが、そこまでこだわる必要は無いかと思います。迷ったら開発メンバーに助言を求めれば良いと思います。コーディング規約があるのであれば、それに則って書けばOKです。ただし、どの書き方がベストなのか、個人の判断で決めるのは避けた方が良いかと。
私の場合は、EXISTS・NOT EXISTSのサブクエリは次の様に書いています。あくまでも例の一つに過ぎません。
SELECT *
FROM TABLE_A TAB_A
WHERE EXISTS (SELECT 1
FROM TABLE_B TAB_B
WHERE TAB_B.COL_1 = TAB_A.COL_1);
Oracleに最適なのは何か、という議論はインターネット上で盛んに行われていますが、実際の現場業務の開発チームで適したもの（レビューでOKが出たもの）を採用すれば良いです。
関連記事
↓別テーブルの値で更新（Update）する方法
参考サイト
.
