LEFT JOIN でテーブルのすべての行を残し、IS NULL で不在を見つけ、COALESCE でそれを置き換えましょう。
このレッスンを Kodokon で開く前のレッスンの INNER JOIN は、注文のない顧客を隠してしまいます。ところが、実務で最も多い問いはまさに、誰が欠けているのか？ です。休眠中の顧客、一度も売れなかった商品、支払いのない請求書など。このスクリプトを sqliteonline.com か sqlite3 で実行しましょう。注文のない四人目の顧客を追加します。
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount REAL NOT NULL
);
INSERT INTO customers (id, name, city) VALUES
(1, 'Alice', 'Lyon'),
(2, 'Karim', 'Paris'),
(3, 'Mina', 'Nantes'),
(4, 'Paulo', 'Lille');
INSERT INTO orders (id, customer_id, amount)
VALUES
(1, 1, 49.90),
(2, 1, 15.00),
(3, 2, 120.50);LEFT JOIN は、右側に一致相手がなくても、左側のテーブル（キーワードの前に書かれたテーブル）のすべての行を残します。一致相手がないとき、右側のテーブルの列は NULL で埋められます。
SELECT c.name, o.id AS order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id;欠けているものを取り出すには、これらの NULL 値で絞り込みます。これはアンチ結合パターンで、データ分析における欠かせない反射神経です。テストする列は右側のテーブルから来ていて、自然にNULLになることが決してないものでなければなりません。だからその主キーである o.id を選ぶのです。
SELECT c.name, c.city
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE o.id IS NULL;最後にもう一つの道具です。COALESCE(a, b) は、a がNULLでなければ a を、そうでなければ b を返します。LEFT JOIN と集計と組み合わせると、レポートや請求書の中で、不在を使える形のゼロに変えてくれます。
SELECT c.name,
COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
GROUP BY c.id
ORDER BY total_spent DESC;