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

内部結合と外部結合

三人の顧客と四件の注文を用意します。内部・左外部・右外部・完全外部結合を同じ表で実行し、対応しない行を確認します。支払済みという条件をONとWHEREへ移し、残る顧客の違いまで調べます。SQLiteを使い、顧客IDがNULLの注文は会員登録のない注文を表します。

説明

JOINは二つの表の行を条件で対応させます。対応が見つかった組だけを返す内部結合と、対応がない側の行も残す外部結合では、注文のない顧客などの表示が変わります。

三人の顧客と四件の注文を用意します。内部・左外部・右外部・完全外部結合を同じ表で実行し、対応しない行を確認します。支払済みという条件をONとWHEREへ移し、残る顧客の違いまで調べます。SQLiteを使い、顧客IDがNULLの注文は会員登録のない注文を表します。

1顧客と注文の二表

顧客3には注文がなく、注文104には顧客IDがありません。

CREATE TABLE customers顧客の表(id INTEGER PRIMARY KEY,name TEXT) STRICT; INSERT INTO customers VALUES(1,'あおい'),(2,'はる'),(3,'みお'); CREATE TABLE orders顧客IDで参照する注文の表(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;

顧客1には二件、顧客2には一件が対応します。注文のstatusはpaidが支払済み、pendingが支払待ち、cancelledが取消しです。外部キー列はNULLを許すので、注文104も保存できます。

2対応した組だけを返す内部結合

顧客のidと注文のcustomer_idが等しい組を取得します。

SELECT c.id AS customer,c.name,o.id AS order_id,o.status FROM customers AS c顧客表の別名 INNER JOIN orders AS o注文表との内部結合 ON o.customer_id=c.id二表の対応条件 ORDER BY c.id,o.id;

注文101・102・103の三行です。cとoは表の別名で、どちらの列かを示します。顧客3には一致する注文がなく、注文104はNULLとの等号が真にならないため、いずれも結果に入りません。

3顧客を全員残す左外部結合

INNER JOINをLEFT JOINへ変えます。

SELECT c.id AS customer,c.name,o.id AS order_id,o.status FROM customers AS c LEFT JOIN orders AS o左側の顧客をすべて残す ON o.customer_id=c.id注文との対応条件 ORDER BY c.id,o.id;

顧客3の行が加わり、注文の列がNULLになります。LEFT JOINは左表の行を残し、一致する右表の行がなければ右側をNULLで補います。顧客1に対応する二件はそのまま二行です。

4注文を全件残す右外部結合

同じ表の並びでRIGHT JOINにします。

SELECT c.id AS customer,c.name,o.id AS order_id,o.status FROM customers AS c RIGHT JOIN orders AS o右側の注文をすべて残す ON o.customer_id=c.id ORDER BY o.id;

注文104が入り、顧客側がNULLです。RIGHT JOINは右表の注文を残します。顧客3は注文と対応しないので、この結果には含まれません。左右はFROMに書いた表の位置を指します。

5両方の未一致行を残す

FULL OUTER JOINを使います。

SELECT c.id AS customer,c.name,o.id AS order_id,o.status FROM customers AS c FULL OUTER JOIN orders AS o左右両方の未一致行も残す ON o.customer_id=c.id ORDER BY c.id,o.id;

五行になり、顧客3と注文104の両方が残ります。一致する三組に、左だけの一行、右だけの一行を加えた結果です。SQLiteの昇順ではNULLが先になるため、注文104が先頭です。

6結合後に支払済みへ絞る

LEFT JOINの後へWHEREを付けます。

SELECT c.id AS customer,c.name,o.id AS order_id FROM customers AS c LEFT JOIN orders AS oまず顧客を残す結合 ON o.customer_id=c.id WHERE o.status='paid'結合結果を支払済みへ絞る ORDER BY c.id,o.id;

あおいの注文101だけになります。WHEREは結合後の結果へ適用されます。顧客3の補われたstatusはNULLなので条件が真にならず消え、顧客2のcancelledも除外されます。LEFT JOINを書くだけでは、後のWHEREから顧客行を保護できません。

7支払済み注文だけを対応させる

statusの条件をONへ移します。

SELECT c.id AS customer,c.name,o.id AS order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id=c.id AND o.status='paid'対応させる注文の条件 ORDER BY c.id,o.id;

全顧客が残り、あおいには101、はるとみおにはNULLが付きます。ONはどの注文を一致とみなすかを決めます。支払済みの一致がない顧客でも、左表の行として残る点が前のWHEREとの違いです。

8注文が一件もない顧客

全注文と結合した後で、注文IDがNULLの行を取得します。

SELECT c.id,c.name FROM customers AS c LEFT JOIN orders AS o ON o.customer_id=c.id WHERE o.id IS NULL一致する注文がない印 ORDER BY c.id;

みおだけです。orders.idは主キーで実在する行ではNULLにならないため、ここでのNULLは結合の相手がなかった印です。NULLを許す別の列で判定すると、実在する注文を未一致と誤認する可能性があります。

9支払済み注文がない顧客

ONを支払済みに限定してから、未一致を取得します。

SELECT c.id,c.name FROM customers AS c LEFT JOIN orders AS o ON o.customer_id=c.id AND o.status='paid'支払済みだけを一致とする WHERE o.id IS NULLその条件での未一致 ORDER BY c.id;

はるとみおです。はるには注文自体はありますが、支払済みはありません。未一致が何を意味するかはONの条件で変わります。「注文なし」と「支払済み注文なし」は別の検索です。

10対応表を確かめる

元の注文ID・顧客ID・状態を再表示します。

SELECT id,customer_id,status結合の元になった列 FROM orders ORDER BY id;

101と102の顧客IDは1、103は2、104はNULLのままです。結合条件の変更で保存された参照を書き換えたわけではありません。必要な一覧に応じて、残す側と一致条件を選びます。

まとめ

内部結合は一致した組、外部結合は指定した側の未一致行も返します。

ONは一致とみなす条件、WHEREは結合後に残す行の条件です。

未一致の判定には、実在する行でNULLにならない列を使います。

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

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 c.id AS customer,c.name,o.id AS order_id,o.status FROM customers AS c INNER JOIN orders AS o ON o.customer_id=c.id ORDER BY c.id,o.id;
╭──────────┬────────┬──────────┬───────────╮
│ customer │  name  │ order_id │  status   │
╞══════════╪════════╪══════════╪═══════════╡
│        1 │ あおい │      101 │ paid      │
│        1 │ あおい │      102 │ pending   │
│        2 │ はる   │      103 │ cancelled │
╰──────────┴────────┴──────────┴───────────╯
3
SELECT c.id AS customer,c.name,o.id AS order_id,o.status FROM customers AS c LEFT JOIN orders AS o ON o.customer_id=c.id ORDER BY c.id,o.id;
╭──────────┬────────┬──────────┬───────────╮
│ customer │  name  │ order_id │  status   │
╞══════════╪════════╪══════════╪═══════════╡
│        1 │ あおい │      101 │ paid      │
│        1 │ あおい │      102 │ pending   │
│        2 │ はる   │      103 │ cancelled │
│        3 │ みお   │ NULL     │ NULL      │
╰──────────┴────────┴──────────┴───────────╯
4
SELECT c.id AS customer,c.name,o.id AS order_id,o.status FROM customers AS c RIGHT JOIN orders AS o ON o.customer_id=c.id ORDER BY o.id;
╭──────────┬────────┬──────────┬───────────╮
│ customer │  name  │ order_id │  status   │
╞══════════╪════════╪══════════╪═══════════╡
│        1 │ あおい │      101 │ paid      │
│        1 │ あおい │      102 │ pending   │
│        2 │ はる   │      103 │ cancelled │
│ NULL     │ NULL   │      104 │ paid      │
╰──────────┴────────┴──────────┴───────────╯
5
SELECT c.id AS customer,c.name,o.id AS order_id,o.status FROM customers AS c FULL OUTER JOIN orders AS o ON o.customer_id=c.id ORDER BY c.id,o.id;
╭──────────┬────────┬──────────┬───────────╮
│ customer │  name  │ order_id │  status   │
╞══════════╪════════╪══════════╪═══════════╡
│ NULL     │ NULL   │      104 │ paid      │
│        1 │ あおい │      101 │ paid      │
│        1 │ あおい │      102 │ pending   │
│        2 │ はる   │      103 │ cancelled │
│        3 │ みお   │ NULL     │ NULL      │
╰──────────┴────────┴──────────┴───────────╯
6
SELECT c.id AS customer,c.name,o.id AS order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id=c.id WHERE o.status='paid' ORDER BY c.id,o.id;
╭──────────┬────────┬──────────╮
│ customer │  name  │ order_id │
╞══════════╪════════╪══════════╡
│        1 │ あおい │      101 │
╰──────────┴────────┴──────────╯
7
SELECT c.id AS customer,c.name,o.id AS order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id=c.id AND o.status='paid' ORDER BY c.id,o.id;
╭──────────┬────────┬──────────╮
│ customer │  name  │ order_id │
╞══════════╪════════╪══════════╡
│        1 │ あおい │      101 │
│        2 │ はる   │ NULL     │
│        3 │ みお   │ NULL     │
╰──────────┴────────┴──────────╯
8
SELECT c.id,c.name FROM customers AS c LEFT JOIN orders AS o ON o.customer_id=c.id WHERE o.id IS NULL ORDER BY c.id;
╭────┬──────╮
│ id │ name │
╞════╪══════╡
│  3 │ みお │
╰────┴──────╯
9
SELECT c.id,c.name FROM customers AS c LEFT JOIN orders AS o ON o.customer_id=c.id AND o.status='paid' WHERE o.id IS NULL ORDER BY c.id;
╭────┬──────╮
│ id │ name │
╞════╪══════╡
│  2 │ はる │
│  3 │ みお │
╰────┴──────╯
10
SELECT id,customer_id,status FROM orders ORDER BY id;
╭─────┬─────────────┬───────────╮
│ id  │ customer_id │  status   │
╞═════╪═════════════╪═══════════╡
│ 101 │           1 │ paid      │
│ 102 │           1 │ pending   │
│ 103 │           2 │ cancelled │
│ 104 │ NULL        │ paid      │
╰─────┴─────────────┴───────────╯

演習完了