SQL Subqueries Explained: Scalar, Correlated & Syntax Guide

Imagine asking: "Which orders were larger than our overall average order value?"

You cannot answer that in a flat, single-stage query without hardcoding numbers, because you need two pieces of information simultaneously: the individual transaction amounts, and the global average computed across all 2,000 transactions.

The answer is a subquery—a query nested inside another query. But while subqueries are an essential building block in an analyst's toolkit, they also introduce significant performance pitfalls and logic bugs, including the infamous NOT IN + NULL trap that silently returns zero rows in technical interviews.

In this guide, we break down scalar, list, and correlated subqueries, expose why correlated subqueries cause O(N × M) slowdowns, and demonstrate when to refactor messy nested subqueries into readable Common Table Expressions (CTEs) using verified PostgreSQL data. For foundational query skills, see our SQL GROUP BY vs HAVING Guide and SQL CTE Guide.

1. Scalar Subqueries: Comparing Against a Computed Benchmark

A scalar subquery returns exactly one row and one column—a single atomic value. Because it evaluates to a single scalar, you can place it anywhere a literal value, column name, or constant is expected (such as in WHERE, SELECT, or HAVING).

Finding Orders Above Average

First, compute the overall average:

SELECT ROUND(AVG(total_amount), 2) AS overall_avg
FROM orders;

Result: ₹391.45

Instead of hardcoding 391.45 (which becomes stale the second new orders arrive), drop the subquery directly into the WHERE clause:

SELECT order_id,
       total_amount
FROM orders
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM orders
)
ORDER BY total_amount DESC;

The Output

• Total Orders in Database: 2,000
• Orders Above Average: Exactly 1,063 rows
• Smallest Returned Order: ₹391.50 (just above the ₹391.45 threshold)

The database executes the inner query once, computes ₹391.45, and then filters the outer query against that dynamic threshold.

2. Multi-Row Subqueries with IN: Filtering by a List

A subquery can also return a single column containing multiple rows. You test whether an outer value exists within that list using the IN operator.

Finding Products with Customer Reviews

Our database contains 200 products. Which ones have received at least one customer review?

SELECT product_id,
       product_name,
       category
FROM products
WHERE product_id IN (
    SELECT DISTINCT product_id
    FROM reviews
)
ORDER BY product_id
LIMIT 5;

The inner query scans the reviews table and compiles a list of reviewed product_ids. The outer query matches products against that set.

3. The Interview Gotcha: The Deadly NOT IN + NULL Trap

Now consider the inverse business question: "Which products have NEVER received a review?"

Natural instinct suggests changing IN to NOT IN:

SELECT product_id,
       product_name
FROM products
WHERE product_id NOT IN (
    SELECT product_id
    FROM reviews
);

In our clean sample table where product_id is a primary key, this works. But look at what happens in production if the subquery contains even a single NULL value.

Why NOT IN Fails on NULLs

Suppose the subquery returns the list (101, 102, NULL). The NOT IN condition expands mathematically to:

WHERE (product_id <> 101)
  AND (product_id <> 102)
  AND (product_id <> NULL)

In SQL three-valued logic, any equality comparison with NULL returns UNKNOWN:

TRUE AND TRUE AND UNKNOWN  →  UNKNOWN

Because WHERE only retains rows where the condition evaluates strictly to TRUE, an UNKNOWN result causes the database to silently discard all rows, returning an empty table.

┌──────────────────────────────────────────────────────────┐
│              THE NOT IN vs NULL DANGER                   │
├──────────────────────────────────────────────────────────┤
│  List: (101, 102, NULL)                                  │
│  Evaluation: (id <> 101) AND (id <> 102) AND (id <> NULL)│
│  Result for every row: UNKNOWN                           │
│  Final Query Output: 0 ROWS (Silent Failure!)            │
└──────────────────────────────────────────────────────────┘

The Safe Alternatives: NOT EXISTS and Anti-Joins

To protect production queries against unexpected NULLs, always use NOT EXISTS or a LEFT JOIN ... IS NULL (Anti-Join):

-- ✅ SOLUTION 1: NOT EXISTS (Immune to NULLs)
SELECT p.product_id,
       p.product_name
FROM products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM reviews AS r
    WHERE r.product_id = p.product_id
);
 
-- ✅ SOLUTION 2: LEFT JOIN Anti-Join (High Performance)
SELECT p.product_id,
       p.product_name
FROM products AS p
LEFT JOIN reviews AS r ON p.product_id = r.product_id
WHERE r.product_id IS NULL;

Both solutions are completely immune to the NULL trap and allow query optimizers to execute efficient index-backed anti-joins.

4. Correlated Subqueries: Row-by-Row Evaluation

An uncorrelated subquery executes once independently. A correlated subquery, by contrast, references columns from the outer query table, requiring the inner query to re-execute for every single candidate row in the outer query.

Business Question: Orders Above the Average of Their OWN Status

SELECT o.order_id,
       o.status,
       o.total_amount
FROM orders AS o
WHERE o.total_amount > (
    SELECT AVG(x.total_amount)
    FROM orders AS x
    WHERE x.status = o.status -- ← Correlation: inner query references outer row 'o'
)
ORDER BY o.status, o.total_amount DESC
LIMIT 10;

The Mechanism

1. For each of the 2,000 rows in orders (o), the database passes o.status to the inner query.
2. The inner query computes the average for that specific status (e.g. Completed AOV: ₹486.22, Refunded AOV: -₹498.01).
3. The outer query checks if o.total_amount beats that status-specific average.

5. When to Avoid Subqueries: Refactoring into CTEs

While single-level scalar subqueries are clean, nesting subqueries 2, 3, or 4 levels deep produces unreadable, unmaintainable code that must be parsed inside-out.

The Problem: Multi-Level Nested Subqueries

Look at this complex nested query trying to compare monthly revenue against the average monthly revenue:

-- ❌ MESSY INSIDE-OUT NESTED SUBQUERY
SELECT m.month,
       m.revenue
FROM (
    SELECT DATE_TRUNC('month', order_date)::date AS month,
           SUM(total_amount)                     AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date)::date
) AS m
WHERE m.revenue > (
    SELECT AVG(monthly_rev)
    FROM (
        SELECT SUM(total_amount) AS monthly_rev
        FROM orders
        WHERE status = 'completed'
        GROUP BY DATE_TRUNC('month', order_date)::date
    ) AS sub
)
ORDER BY m.month;

The Refactor: Clean, Top-to-Bottom CTEs

Common Table Expressions (WITH clause) let you name each logical step like a recipe:

-- ✅ CLEAN TOP-TO-BOTTOM CTE
WITH monthly_revenue AS (
    -- Step 1: Calculate monthly completed revenue
    SELECT DATE_TRUNC('month', order_date)::date AS month,
           SUM(total_amount)                     AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY month
),
overall_monthly_avg AS (
    -- Step 2: Compute average across all months
    SELECT AVG(revenue) AS avg_monthly_rev
    FROM monthly_revenue
)
-- Step 3: Filter months beating the benchmark
SELECT m.month,
       ROUND(m.revenue, 2)                     AS revenue,
       ROUND(a.avg_monthly_rev, 2)             AS benchmark_avg
FROM monthly_revenue AS m
CROSS JOIN overall_monthly_avg AS a
WHERE m.revenue > a.avg_monthly_rev
ORDER BY m.month;

Why the CTE Wins

• Reads Top-to-Bottom: Step 1 builds the base aggregation; Step 2 computes the benchmark; Step 3 filters.
• Eliminates Code Duplication: monthly_revenue is defined once and referenced twice, rather than recalculating the same GROUP BY block.
• Easy to Debug: You can test SELECT * FROM monthly_revenue in isolation by commenting out subsequent steps.

6. Subquery vs CTE vs Join Comparison

7. Summary & Practice

• Use scalar subqueries for dynamic single-value filters (WHERE amount > (SELECT AVG(...))).
• Never use NOT IN with subqueries on nullable columns—replace with NOT EXISTS or anti-joins.
• Watch out for correlated subquery slowdowns—replace row-by-row lookups with window functions or pre-aggregated joins.
• Refactor nested subqueries into CTEs whenever logic spans more than one level.

Sharpen your query optimization and subquery refactoring skills in the Topfolio Interactive SQL Sandbox or enroll in our comprehensive Data Analyst Career Track. To master advanced query framing, read our SQL CTE Guide and SQL Window Functions Guide.