LEFT/RIGHT JOINとOUTER JOIN

LEFT JOIN：左側のすべての行を保持する

SELECT u.id, u.email, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Users without orders still appear, with order_id = NULL.

RIGHT JOIN

SELECT u.id, u.email, o.id
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;

-- Equivalent to LEFT JOIN with sides swapped:
SELECT u.id, u.email, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

FULL OUTER JOIN

SELECT u.email, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

一致しない行を見つける

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;        -- users who have never placed an order

LEFT JOINと集約

SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
ORDER BY order_count DESC;

フィルタの位置が重要

-- Drops users without paid orders entirely:
SELECT u.id, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.id;

-- Keeps users with no paid orders:
SELECT u.id, COUNT(o.id)
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id AND o.status = 'paid'
GROUP BY u.id;

複数のLEFT JOIN

SELECT u.email, o.id, p.name
FROM users u
LEFT JOIN orders o   ON o.user_id = u.id
LEFT JOIN products p ON p.id = o.product_id;

LEFT JOINのパフォーマンス

外部結合とNULLの出力

SELECT u.email, COALESCE(SUM(o.total), 0) AS revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.email;

CROSS JOIN — すべての組み合わせが必要な場合

SELECT d.date, u.id
FROM users u
CROSS JOIN generate_series('2024-01-01', '2024-01-07', INTERVAL '1 day') AS d(date);

アンチJOINのパターン

-- LEFT JOIN ... WHERE IS NULL
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- NOT EXISTS (often the planner's favourite)
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

まとめ

確認問題