SQLのEXISTSとNOT EXISTSとは?相関条件・注文の有無・NOT INとの違い
EXISTSは、サブクエリの結果が1行でもあるかを調べる式です。NOT EXISTSは、対応する行がないかを調べます。顧客と注文を使い、注文がある顧客、支払済みの注文がある顧客、未注文の顧客を選び分けましょう。
例で使う表
customersは1行が顧客1件、ordersは1行が注文1件です。customer_idが顧客のidに対応し、amountは金額(円)、statusはpaid(支払済み)またはcancelled(取消)です。青木商店は支払済み2件、北野物産は取消1件、中央商事は未注文、大和食品は支払済みと取消が各1件です。注文106は顧客未入力です。ORDER BYなしの結果の行順は一例で、保証されません。
| id | name |
|---|---|
| 1 | 青木商店 |
| 2 | 北野物産 |
| 3 | 中央商事 |
| 4 | 大和食品 |
| id | customer_id | amount | status |
|---|---|---|---|
| 101 | 1 | 12000 | paid |
| 102 | 1 | 20000 | paid |
| 103 | 2 | 8000 | cancelled |
| 104 | 4 | 30000 | paid |
| 105 | 4 | 9000 | cancelled |
| 106 | NULL | 5000 | paid |
EXISTS・NOT EXISTSの基本構文
SELECT EXISTS (
SELECT 1
FROM orders
);EXISTSは1行でもあればtrueを返す
EXISTSは、カッコ内のSELECTが1行以上返すとtrue、0行ならfalseを返す式です。まず顧客別の条件を付けず、ordersに注文があるかを1列で確認します。この表には6件あるのでtrueです。SELECT 1の1は件数ではなく、存在確認に使う値です。2件でも6件でも、EXISTSの結果はtrueのままです。
SELECT EXISTS (
SELECT 1
FROM orders
);| exists |
|---|
| true |
相関条件で「その顧客の注文」を調べる
顧客ごとの注文の有無は、orders.customer_id = customers.idで外側の顧客に対応させます。青木商店は2件、北野物産は1件、大和食品は2件の注文があるので残ります。中央商事は0件なので入りません。条件が成立した顧客の元の行を残すため、青木商店に2件あっても顧客は1行です。相関条件を外すと全顧客について同じ注文表の存在を調べることになり、未注文の中央商事も残ってしまいます。
SELECT *
FROM customers
WHERE EXISTS (
SELECT 1
FROM orders
WHERE orders.customer_id = customers.id
);| id | name |
|---|---|
| 1 | 青木商店 |
| 2 | 北野物産 |
| 4 | 大和食品 |
条件付きEXISTSで支払済みの注文がある顧客を選ぶ
相関条件にorders.status = 'paid'をANDで加えると、その顧客の支払済み注文だけを存在確認できます。青木商店と大和食品が残ります。北野物産には取消注文しかないので対象外です。大和食品には取消注文もありますが、支払済みが1件あるのでこの条件を満たします。EXISTSはすべての注文が支払済みかを調べる条件ではありません。顧客IDがNULLの支払済み注文は、どの顧客にも対応しません。
SELECT *
FROM customers
WHERE EXISTS (
SELECT 1
FROM orders
WHERE orders.customer_id = customers.id
AND orders.status = 'paid'
);| id | name |
|---|---|
| 1 | 青木商店 |
| 4 | 大和食品 |
NOT EXISTSで対応する注文がない顧客を選ぶ
NOT EXISTSは、内側のSELECTが0行の場合にtrueになります。同じ相関条件で調べると、注文が1件もない中央商事だけが残ります。取消しかない北野物産も、注文自体は存在するので入りません。注文のcustomer_idにNULLがあっても、それは中央商事のIDと一致する行にはならず、中央商事の未注文判定を妨げません。
SELECT *
FROM customers
WHERE NOT EXISTS (
SELECT 1
FROM orders
WHERE orders.customer_id = customers.id
);| id | name |
|---|---|
| 3 | 中央商事 |
EXISTS・NOT EXISTSでよくある間違い
NOT INへ置き換えるとNULLで結果が変わる
この表でid NOT IN (SELECT customer_id FROM orders)とすると、候補にNULLがあるため中央商事も条件を通らず、結果は0行になります。NOT EXISTSは顧客に対応する行の不在を調べ、中央商事を返します。両者は常に同じ結果になるわけではありません。
SELECT *
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);1つの注文の条件と、別の注文の不在を混同しない
「支払済み注文がある、かつ取消注文がない」を選ぶには、支払済みのEXISTSと取消のNOT EXISTSを分けます。paid AND status <> 'cancelled'を同じ注文に指定しても、別の取消注文がある大和食品を除外できません。
実際に1問解いてみる
EXISTS
ordersに注文が1件でもあるかをEXISTSで確認し、真偽値を1行1列で取得してください。別名は付けません。
関連構文
EXISTS・NOT EXISTSについてよくある質問
- EXISTSとJOINはどう使い分けますか?
- 注文の列も一緒に取得するならJOIN、注文の有無だけで顧客を選ぶならEXISTSが使えます。この表を単純にINNER JOINすると青木商店は注文2件分の2行になります。EXISTSは条件を満たす元の顧客行を1行のまま残します。
- 相関条件はどこに書きますか?
- この例では、内側のWHEREにorders.customer_id = customers.idを書きます。外側のcustomersの行に対応する注文を選ぶ条件です。省くと、顧客ごとの注文ではなく注文表全体の存在を調べます。
- 支払済み注文がない顧客と、注文がない顧客は同じですか?
- 違います。NOT EXISTSの内側でpaidに絞ると、未注文の中央商事に加えて取消しかない北野物産も該当します。注文そのものがない顧客を探すなら、内側で支払状態を限定しません。
- EXISTSはすべての注文を数えますか?
- 件数を返す式ではありません。行があるかが分かれば判定でき、実際の処理方法はPostgreSQLが決めます。件数そのものを取得するにはCOUNTを使います。
参考文献
内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:EXISTS — EXISTSの真偽値、相関、出力列と行数
- PostgreSQL公式:NOT IN — NULLが候補に含まれる場合のNOT IN