CROSS JOINと直積

SELECT * FROM a CROSS JOIN b;

-- Equivalent:
SELECT * FROM a, b;        -- comma join, no condition

SELECT u.email, o.id
FROM users u, orders o;     -- missing WHERE u.id = o.user_id !
-- m users × n orders rows — millions!

SELECT d.day, s.id, COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series(CURRENT_DATE - 30, CURRENT_DATE, '1 day') AS d(day)
CROSS JOIN stores s
LEFT JOIN orders o ON o.store_id = s.id
                  AND date_trunc('day', o.created_at) = d.day
GROUP BY d.day, s.id
ORDER BY d.day, s.id;

EXPLAIN ANALYZE
SELECT u.email, o.id FROM users u, orders o;
-- Nested Loop  (rows=10000000)  ← warning sign

SELECT a.id, b.id
FROM products a
JOIN products b ON a.id < b.id;
-- All unique pairs of products, no (A,A), no (B,A) duplicates of (A,B)

SELECT u.email, c.code
FROM users u
CROSS JOIN (VALUES ('EUR'), ('USD'), ('GBP')) AS c(code);

-- Don't (looks like a typo):
FROM users, orders

-- Do:
FROM users CROSS JOIN orders