Nest queries inside queries with subqueries and EXISTS, make complex SQL readable with WITH (CTEs), walk hierarchies with recursive CTEs, and combine result sets with UNION, INTERSECT and EXCEPT.
Why: a subquery is a SELECT inside another query. A scalar subquery returns one value you can compare against; an IN subquery returns a list to match within.
-- products priced above the average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);-- users who have placed at least one order
SELECT full_name
FROM users
WHERE id IN (SELECT user_id FROM orders);Why: EXISTS checks whether a related row exists at all, without caring about its values. It is often clearer and faster than IN for "does this user have any orders?" style questions because it can stop at the first match.
SELECT full_name
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);Why: a CTE names a query so you can build a big query out of readable, labelled steps instead of deeply nested subqueries. Each WITH block can be referenced later by name, like a temporary view that exists only for this statement.
WITH big_orders AS (
SELECT user_id, SUM(total) AS spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000
)
SELECT u.full_name, b.spent
FROM big_orders b
JOIN users u ON u.id = b.user_id
ORDER BY b.spent DESC;Why: a recursive CTE repeats a query, feeding its own output back in, until nothing new comes out — the standard tool for walking tree or hierarchy data such as an org chart or a category tree.
-- everyone who reports (directly or indirectly) to employee 1
WITH RECURSIVE reports AS (
SELECT id, name, manager_id
FROM employees
WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN reports r ON e.manager_id = r.id
)
SELECT * FROM reports;Why: these stack two result sets that have the same columns. UNION returns rows in either (dropping duplicates; UNION ALL keeps them), INTERSECT returns rows in both, and EXCEPT returns rows in the first but not the second. We make two small mailing lists to combine — run the setup first.
-- setup: two small mailing lists
CREATE TABLE IF NOT EXISTS customers (email text);
CREATE TABLE IF NOT EXISTS newsletter_subscribers (email text);
INSERT INTO customers (email) VALUES
('ada@example.com'),
('grace@example.com');
INSERT INTO newsletter_subscribers (email) VALUES
('grace@example.com'),
('tim@example.com');-- everyone in either list (duplicates removed)
SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;-- customers who are NOT on the newsletter
SELECT email FROM customers
EXCEPT
SELECT email FROM newsletter_subscribers;