一覧DB-04-3 · JOINと部分問合せ

サブクエリとEXISTS

三人の顧客と四件の注文を用意し、最大額の比較、IN、EXISTSを順に使います。注文のない顧客を探す際のNOT INとNULLの問題を再現し、相関条件を書き忘れた場合に対象が変わる理由も確かめます。

説明

部分問合せはSQLの中に書くSELECTです。一つの値、値の集合、行の存在を条件に使えます。外側の顧客IDを内側から参照する相関部分問合せでは、顧客ごとに対応する注文があるかを表現できます。

三人の顧客と四件の注文を用意し、最大額の比較、IN、EXISTSを順に使います。注文のない顧客を探す際のNOT INとNULLの問題を再現し、相関条件を書き忘れた場合に対象が変わる理由も確かめます。

1顧客と注文を用意する

顧客1に二件、顧客2に一件、顧客3に零件の注文がある状態を作ります。

CREATE TABLE customers顧客の表(id INTEGER PRIMARY KEY,name TEXT) STRICT; INSERT INTO customers VALUES(1,'あおい'),(2,'はる'),(3,'みお'); CREATE TABLE orders注文の表(id INTEGER PRIMARY KEY,customer_id INTEGER REFERENCES customers(id),status TEXT,amount INTEGER) STRICT; INSERT INTO orders VALUES(101,1,'paid',100),(102,1,'pending',200),(103,2,'cancelled',50),(104,NULL,'paid',80); SELECT * FROM customers顧客一覧 ORDER BY id; SELECT * FROM orders注文一覧 ORDER BY id;

注文104は顧客IDがNULLで、会員登録のない注文です。顧客の表には存在しない特別な番号を使わず、参照がない状態として保存しています。このNULLがINとNOT INの結果に関係します。

2全注文の平均より高い注文

内側のSELECTで平均額を求めて条件に使います。

SELECT id,amount FROM orders WHERE amount>(SELECT AVG(amount) FROM orders)条件に使う単一の値 ORDER BY id;

平均は(100+200+50+80)/4=107.5円なので、注文102の200円だけです。括弧内は一行一列の平均値を返し、外側の各注文額と比較されます。

3注文に現れる顧客IDを候補にする

注文のcustomer_idをINの候補にします。

SELECT id,name FROM customers WHERE id IN (SELECT customer_id FROM orders)注文に含まれるIDへ一致 ORDER BY id;

あおいとはるが選ばれます。候補には1が二回ありますが、外側の顧客行を複製することはありません。INは候補のどれかに一致するかを条件にします。

4EXISTSで対応する注文の存在を調べる

内側で外側の顧客IDを参照します。

SELECT c.id,c.name FROM customers AS c WHERE EXISTS (SELECT 1一行でも存在するか FROM orders AS o WHERE o.customer_id=c.id外側の顧客に対応する条件) ORDER BY c.id;

同じ二人が選ばれます。EXISTSは部分問合せが一行以上返すかで真偽を決めます。SELECT 1の1を合計するのではありません。あおいに注文が二件あっても、顧客は一行のままです。

5150円以上の注文を持つ顧客

存在判定の内側へ金額条件を追加します。

SELECT c.id,c.name FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id=c.id顧客ごとの注文 AND o.amount>=150存在してほしい注文の金額条件) ORDER BY c.id;

あおいだけです。顧客IDが一致し、かつ150円以上という両条件を満たす注文102が存在します。どの注文が条件に合うかと、顧客を一行残すかを分けて表現できます。

6相関条件を外すと全顧客が残る

顧客IDの対応条件だけを取り除きます。

SELECT c.id,c.name FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.amount>=150外側の顧客IDを参照しない条件) ORDER BY c.id;

三人とも選ばれます。内側が問うのは「表全体に150円以上の注文があるか」だけになり、外側の顧客によらず真です。顧客3自身に注文があるという意味にはなりません。

7NOT INにNULLが含まれる場合

注文の顧客ID候補に含まれない顧客を探します。

SELECT id,name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders)NULLも含む候補の否定 ORDER BY id;

結果は0行です。顧客1と2は候補にあるため偽、顧客3は候補内のNULLとの比較を確定できずNULLになります。WHEREはNULLの条件を残さないので、注文のないみおも出ません。

8候補からNULLを除いて比較する

内側へIS NOT NULLを追加します。

SELECT id,name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL候補からNULLを除く) ORDER BY id;

みおだけになります。この例の外側idは主キーでNULLではありません。内側のNULLを除けば、残る候補1・1・2のどれにも一致しない3を確定できます。

9対応する注文が存在しない顧客

NOT EXISTSで顧客に対応する注文の不存在を判定します。

SELECT c.id,c.name FROM customers AS c WHERE NOT EXISTS (SELECT 1対応する行が一件もないか FROM orders AS o WHERE o.customer_id=c.id顧客ごとの対応条件) ORDER BY c.id;

みおだけです。顧客IDの一致する注文行が一行もないため、NOT EXISTSが真になります。注文104のNULLは誰とも一致せず、他の顧客の不存在判定を妨げません。

10顧客ごとの最大注文額を列として返す

相関部分問合せで最大額を一列へ表示します。

SELECT c.id,c.name,(SELECT MAX(o.amount)顧客ごとの最大額 FROM orders AS o WHERE o.customer_id=c.id)外側の顧客に限定 AS max_amount FROM customers AS c ORDER BY c.id;

あおい200円、はる50円、みおNULLです。MAXの集約は該当注文がなくてもNULLの一行を返します。外側の三人はWHEREで絞っていないので全員残ります。

11複数行を返す部分問合せを単一値に使うとき

SQLiteの単一値用の部分問合せで最古の注文を明示します。

SELECT c.id,(SELECT o.amount FROM orders AS o WHERE o.customer_id=c.id ORDER BY o.id LIMIT 1どの一行を選ぶか明示) AS first_amount FROM customers AS c ORDER BY c.id;

あおい100円、はる50円、みおNULLです。SQLiteは単一値の部分問合せで先頭行を使いますが、ORDER BYなしの先頭を期待するべきではありません。ここでは注文IDの小さい順とLIMIT 1を明示し、最大額とは別の値を求めています。

まとめ

部分問合せを単一値・候補集合・存在判定のどれとして使うかを明確にします。

EXISTSの相関条件を外すと、顧客ごとの存在判定から表全体の存在判定へ変わります。

NOT INのNULLを考慮し、対応行がないことはNOT EXISTSでも表現できます。

操作履歴と解説を振り返れます。

SQLite 3.54.0

1
CREATE TABLE customers(id INTEGER PRIMARY KEY,name TEXT) STRICT; INSERT INTO customers VALUES(1,'あおい'),(2,'はる'),(3,'みお'); CREATE TABLE orders(id INTEGER PRIMARY KEY,customer_id INTEGER REFERENCES customers(id),status TEXT,amount INTEGER) STRICT; INSERT INTO orders VALUES(101,1,'paid',100),(102,1,'pending',200),(103,2,'cancelled',50),(104,NULL,'paid',80); SELECT * FROM customers ORDER BY id; SELECT * FROM orders ORDER BY id;
╭────┬────────╮
│ id │  name  │
╞════╪════════╡
│  1 │ あおい │
│  2 │ はる   │
│  3 │ みお   │
╰────┴────────╯
╭─────┬─────────────┬───────────┬────────╮
│ id  │ customer_id │  status   │ amount │
╞═════╪═════════════╪═══════════╪════════╡
│ 101 │           1 │ paid      │    100 │
│ 102 │           1 │ pending   │    200 │
│ 103 │           2 │ cancelled │     50 │
│ 104 │ NULL        │ paid      │     80 │
╰─────┴─────────────┴───────────┴────────╯
2
SELECT id,amount FROM orders WHERE amount>(SELECT AVG(amount) FROM orders) ORDER BY id;
╭─────┬────────╮
│ id  │ amount │
╞═════╪════════╡
│ 102 │    200 │
╰─────┴────────╯
3
SELECT id,name FROM customers WHERE id IN (SELECT customer_id FROM orders) ORDER BY id;
╭────┬────────╮
│ id │  name  │
╞════╪════════╡
│  1 │ あおい │
│  2 │ はる   │
╰────┴────────╯
4
SELECT c.id,c.name FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id=c.id) ORDER BY c.id;
╭────┬────────╮
│ id │  name  │
╞════╪════════╡
│  1 │ あおい │
│  2 │ はる   │
╰────┴────────╯
5
SELECT c.id,c.name FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id=c.id AND o.amount>=150) ORDER BY c.id;
╭────┬────────╮
│ id │  name  │
╞════╪════════╡
│  1 │ あおい │
╰────┴────────╯
6
SELECT c.id,c.name FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.amount>=150) ORDER BY c.id;
╭────┬────────╮
│ id │  name  │
╞════╪════════╡
│  1 │ あおい │
│  2 │ はる   │
│  3 │ みお   │
╰────┴────────╯
7
SELECT id,name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders) ORDER BY id;
8
SELECT id,name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL) ORDER BY id;
╭────┬──────╮
│ id │ name │
╞════╪══════╡
│  3 │ みお │
╰────┴──────╯
9
SELECT c.id,c.name FROM customers AS c WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id=c.id) ORDER BY c.id;
╭────┬──────╮
│ id │ name │
╞════╪══════╡
│  3 │ みお │
╰────┴──────╯
10
SELECT c.id,c.name,(SELECT MAX(o.amount) FROM orders AS o WHERE o.customer_id=c.id) AS max_amount FROM customers AS c ORDER BY c.id;
╭────┬────────┬────────────╮
│ id │  name  │ max_amount │
╞════╪════════╪════════════╡
│  1 │ あおい │        200 │
│  2 │ はる   │         50 │
│  3 │ みお   │ NULL       │
╰────┴────────┴────────────╯
11
SELECT c.id,(SELECT o.amount FROM orders AS o WHERE o.customer_id=c.id ORDER BY o.id LIMIT 1) AS first_amount FROM customers AS c ORDER BY c.id;
╭────┬──────────────╮
│ id │ first_amount │
╞════╪══════════════╡
│  1 │          100 │
│  2 │           50 │
│  3 │ NULL         │
╰────┴──────────────╯
演習完了