Joins and Subqueries

SELECT u.email, o.id
FROM users u
JOIN orders o ON o.user_id = u.id;
-- only users who have orders

SELECT u.id, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- order_id NULL if no order

SELECT *
FROM orders o
JOIN order_items i USING (order_id);
-- join on order_id

SELECT u.id, r.score
FROM users u
JOIN LATERAL (
  SELECT score FROM scores s
  WHERE s.user_id = u.id
  ORDER BY score DESC
  LIMIT 1
) r ON true;
-- top score per user

SELECT u.id
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100
);
-- users with a large order

SELECT name,
  (SELECT count(*) FROM orders o WHERE o.user_id = u.id) AS n
FROM users u;
-- n: order count

SELECT *
FROM a
FULL OUTER JOIN b ON a.id = b.a_id;
-- nulls on either side when no match

SELECT c.code, d.day
FROM currencies c
CROSS JOIN days d;
-- every currency × day

SELECT u.*
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- users without orders

SELECT u.*
FROM users u
WHERE EXISTS (SELECT 1 FROM sessions s WHERE s.user_id = u.id);
-- users with any session

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- manager name or null

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

SELECT u.id, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'open';
-- open filter in ON keeps users without open orders

SELECT d.user_id, d.n
FROM (
  SELECT user_id, count(*) AS n FROM orders GROUP BY user_id
) d
WHERE d.n > 5;
-- users with >5 orders

SELECT id FROM a
UNION ALL
SELECT id FROM b;
-- all ids from both