Working with Subqueries
1. Using Subquery in WHERE Clause
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country='US');| Form | When |
|---|---|
| Scalar | Returns one value |
| Row | Returns single tuple |
| Set (IN/EXISTS) | Returns N rows |
2. Using Subquery in FROM Clause
SELECT s.customer_id, s.total
FROM (SELECT customer_id, sum(total_cents) AS total
FROM orders GROUP BY customer_id) s
WHERE s.total > 100000;3. Using Subquery in SELECT Clause
SELECT id, (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;Warning: Scalar subqueries execute once per outer row; rewrite as LEFT JOIN + GROUP BY for large datasets.
4. Using Correlated Subqueries
SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);| Property | Detail |
|---|---|
| References | Outer query column(s) |
| Cost | One execution per outer row (planner may optimize) |
5. Using EXISTS and NOT EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);| Operator | NULL-safe |
|---|---|
| EXISTS | Yes |
| NOT EXISTS | Yes (preferred over NOT IN) |
6. Using IN with Subquery
SELECT * FROM products WHERE id IN (SELECT product_id FROM order_items);7. Using NOT IN with Subquery
SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM order_items WHERE product_id IS NOT NULL);Warning: Always filter NULLs from the inner subquery, otherwise no rows return.
8. Using ANY Operator
SELECT * FROM orders WHERE total_cents > ANY (SELECT min_high FROM tiers);
-- '= ANY (...)' is equivalent to IN9. Using ALL Operator
SELECT * FROM products WHERE price >= ALL (SELECT price FROM products);10. Using SOME Operator
| Synonym | Behavior |
|---|---|
| SOME | Alias for ANY (SQL standard) |
11. Using Lateral Joins
SELECT c.id, recent.*
FROM customers c
LEFT JOIN LATERAL (
SELECT id, placed_at FROM orders
WHERE customer_id = c.id ORDER BY placed_at DESC LIMIT 3
) recent ON true;| Property | Detail |
|---|---|
| Refers to | Earlier FROM items |
| Use case | Top-N per group, set-returning functions |
12. Understanding Subquery Performance
| Pattern | Better Form |
|---|---|
| Correlated scalar | LEFT JOIN + GROUP BY |
| NOT IN with NULL | NOT EXISTS |
| IN (SELECT ...) | Often turned into semi-join automatically |
| Repeated subqueries | CTE (MATERIALIZED) |