JOINs

-- Sample schema
-- users(id, name)
-- orders(id, user_id, total, status)

-- 1) INNER JOIN — only rows in BOTH tables
SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

-- 2) LEFT JOIN — all users; NULLs for users without orders
SELECT u.name, COALESCE(SUM(o.total), 0) AS revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'
GROUP BY u.id, u.name;

-- 3) FULL OUTER — everything from both sides
SELECT a.name, b.name
FROM tableA a
FULL OUTER JOIN tableB b ON a.key = b.key;

-- 4) USING — shorter when column names match
SELECT u.name, o.total
FROM users u
JOIN orders o USING (user_id);    -- requires user_id column in both

-- 5) Self join — hierarchy / relationships within one table
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- 6) Multi-table join — chain them
SELECT u.name, p.title, c.body
FROM users u
JOIN posts    p ON p.user_id = u.id
JOIN comments c ON c.post_id = p.id
WHERE u.id = 42;

-- 7) Anti-join — find users who have NEVER ordered
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Or with NOT EXISTS (often faster, planner-friendly)
SELECT u.name
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 8) Semi-join — users who have AT LEAST ONE paid order
SELECT u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid');

-- 9) LATERAL — per-row subquery (Postgres extension)
SELECT u.name, t.recent_total
FROM users u,
LATERAL (
    SELECT SUM(total) AS recent_total
    FROM orders
    WHERE user_id = u.id
      AND created_at >= now() - interval '30 days'
) t;

-- 10) Top-N per group via LATERAL
SELECT u.name, recent.title, recent.created_at
FROM users u,
LATERAL (
    SELECT title, created_at FROM posts WHERE user_id = u.id
    ORDER BY created_at DESC LIMIT 3
) recent;

-- 11) JOIN performance tips
--   • Index the JOIN columns on BOTH sides
--   • EXPLAIN ANALYZE — confirm the planner picks a sensible algorithm
--     (Hash Join for big joins, Nested Loop for small, Merge Join when sorted)
--   • Avoid SELECT * on multi-table joins — duplicates large columns
--   • For OR conditions in JOIN, sometimes UNION ALL of two queries is faster

-- 12) Comma-style is the same as CROSS JOIN — careful
SELECT * FROM users u, orders o;            -- CARTESIAN product if no WHERE
SELECT * FROM users u CROSS JOIN orders o;  -- explicit

SELECT u.name, p.title
FROM users u
JOIN posts p ON p.user_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
WHERE u.id = 1;