Subqueries: SQL Analysis Chapter 4

Why this chapter matters

Some business questions are naturally phrased as nested logic, such as find customers above their segment average.

What you will learn

Write scalar, table, and EXISTS subqueries for practical filters.
Use correlated subqueries only when they improve clarity.
Rewrite subqueries into joins when performance or readability benefits.

Understand the core ideas

Subqueries let you express logic in layers when a condition depends on another query result. In PostgreSQL, common forms include scalar subqueries that return one value, table subqueries used in IN, and existence checks with EXISTS. The best form depends on the business question and row grain. For example, if you need products priced above their category average, a correlated scalar subquery reads close to plain language and keeps one output row per product. If you need to test whether at least one related row exists, EXISTS is usually clearer than joining and deduplicating. Keep subqueries narrow by selecting only needed columns, and avoid unnecessary ORDER BY inside subqueries unless paired with LIMIT. Narrow subqueries are easier for humans to reason about and often easier for the planner to optimize.

Correlation means the inner query references columns from the outer row, so PostgreSQL evaluates it with that row context in mind. This is powerful but can be slower when used carelessly on large tables. A practical workflow is to prototype with a correlated subquery, verify semantics, then compare with a join or CTE rewrite if performance matters. Always validate row grain after rewrites because join versions can accidentally duplicate rows if category-level data is not unique. EXISTS is especially useful for yes or no filters because it stops at first match and keeps intent explicit. Avoid over-nesting: two levels are often readable, but deeper stacks hide business rules. If future maintainers struggle to explain the query quickly, split logic into named CTEs while preserving PostgreSQL semantics and test outputs against known examples.

Key terms

scalar subquery: A nested query that returns one value and can be used in expressions or comparisons.
correlated subquery: An inner query that references columns from the current row of the outer query.
EXISTS: A boolean test that is true when the subquery returns at least one row.
semijoin logic: Filtering outer rows based on related-row existence without returning joined duplicates.

Products priced above their category average

Use products(product_id, category_id, name, price) with one row per product. Need output grain one row per product where product price is above the average price of its own category.

Start from products p as the outer table so each candidate output row represents one product.
Use a correlated scalar subquery that computes AVG(price) from products p2 filtered to p2.category_id = p.category_id.
Compare p.price to that category average in WHERE to retain only above-average products.
Order by category_id then descending price so reviewers can scan category context and verify logic quickly.

Result:

SELECT p.product_id, p.category_id, p.name, p.price FROM products p WHERE p.price > (SELECT AVG(p2.price) FROM products p2 WHERE p2.category_id = p.category_id) ORDER BY p.category_id, p.price DESC, p.product_id;

returns one row per qualifying product without cross-category leakage.

A common misconception

Claim: EXISTS and IN always perform and behave exactly the same.

Correction: They can overlap, but semantics with nulls and planner choices can differ. Pick the form that best matches intent, then validate behavior on your PostgreSQL data shape.

Lessons in this chapter

Subquery formsDifferentiate scalar, IN, and EXISTS patterns in PostgreSQL. Read the full guide →
Correlated logicApply row-by-row correlation carefully and verify intent with sample outputs.
IN versus EXISTSPick the form that expresses intent clearly and performs well for dataset size.
Refactoring nested queriesConvert deep nesting into cleaner shapes when maintenance cost is high.

Study task

Find products in PostgreSQL whose price is above the average price within their own category.

Chapter checkpoint

What does EXISTS check in a PostgreSQL subquery?

EXISTS returns true when the subquery yields at least one row.

Subqueries