PostgreSQL — Subqueries

Basic Subquery

SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

Subquery in FROM

SELECT avg_total 
FROM (SELECT AVG(total) as avg_total FROM orders) subquery;

Subquery in SELECT

SELECT name, 
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;

Correlated Subquery

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

ANY / ALL

-- Greater than any
SELECT * FROM products WHERE price > ANY (SELECT price FROM products WHERE category = 'food');

-- Greater than all
SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'food');

Subquery Types

Type	Description
Scalar	Returns single value
Row	Returns single row
Table	Returns table
Correlated	References outer query

Mini Practice

1. Use subquery in WHERE
2. Use subquery in FROM
3. Use correlated subquery
4. Use ANY and ALL

Up Next

Continue with CTE — common table expressions.