Working with Subqueries
1. Creating Scalar Subqueries
| Property | Detail |
|---|---|
| Returns | Single row, single column |
| Use | SELECT list, WHERE, SET clauses |
| Error | Multiple rows → "more than one row returned" |
Example: Scalar Subquery
SELECT name, salary,
(SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;2. Using Subqueries in WHERE Clause
| Form | Example |
|---|---|
| IN | WHERE id IN (SELECT customer_id FROM banned) |
| NOT IN | Beware NULLs → use NOT EXISTS |
| ANY / SOME | WHERE price > ANY(SELECT min_price FROM ...) |
| ALL | WHERE price > ALL(SELECT price FROM other) |
3. Using Subqueries in SELECT Clause
| Use | Detail |
|---|---|
| Per-row derived column | One scalar subquery per row |
| Performance | Often slower than join — consider LATERAL or window |
4. Using Subqueries in FROM Clause
Example: Derived Table
SELECT region, AVG(monthly_total) AS avg_month
FROM (
SELECT region, date_trunc('month', placed_at) AS mth, SUM(total) AS monthly_total
FROM orders GROUP BY region, date_trunc('month', placed_at)
) m
GROUP BY region;| Aspect | Detail |
|---|---|
| Alias required | Must give the subquery a name |
| Multi-step aggregation | Common pattern |
| CTE alternative | Often more readable |
5. Using Correlated Subqueries
| Property | Detail |
|---|---|
| Definition | Inner query references outer row |
| Execution | Re-evaluated per outer row (logically) |
| Often rewritable | As JOIN + GROUP BY or window function |
Example: Correlated Subquery
SELECT e.id, e.name
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);6. Testing Existence
| Operator | Use |
|---|---|
| EXISTS | TRUE if subquery returns ≥1 row |
| NOT EXISTS | TRUE if subquery returns 0 rows |
| Best practice | Prefer EXISTS over IN for large inner sets |
| NULL-safe | NOT EXISTS handles NULLs correctly (unlike NOT IN) |
7. Comparing with Subqueries
| Operator | Returns TRUE When |
|---|---|
| x = ANY(subq) | Equal to any returned value (= IN) |
| x > ANY(subq) | Greater than at least one |
| x > ALL(subq) | Greater than every value |
| x <> ALL(subq) | Not equal to any (= NOT IN) |
8. Optimizing Subquery Performance
| Technique | Detail |
|---|---|
| Rewrite to JOIN | Often faster, planner has more options |
| Use EXISTS over IN | Short-circuits; NULL-safe |
| Materialize CTE | Postgres 12+ inlines by default; force with MATERIALIZED |
| LATERAL JOIN | Per-row subqueries with planner help |
| Window functions | Avoid correlated aggregates |
9. Converting Subqueries to Joins
Example: IN → JOIN
-- Subquery form
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE region='EU');
-- JOIN form (often faster)
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id AND c.region='EU';| Subquery | Equivalent Join |
|---|---|
| IN | INNER JOIN (deduplicate as needed) |
| NOT IN / NOT EXISTS | LEFT JOIN ... WHERE right IS NULL (anti-join) |
| EXISTS | SEMI JOIN (planner often picks this) |
10. Using Common Table Expressions
| Form | Detail |
|---|---|
| WITH name AS (...) | Named subquery, scope: outer query |
| Multiple CTEs | Comma-separated; later refers to earlier |
| WITH RECURSIVE | Self-referencing for trees/graphs |
| MATERIALIZED / NOT MATERIALIZED (PG) | Control inlining |