Mastering subquery patterns is the inflection point between writing SQL that works and SQL that scales. This lesson teaches you when to use derived tables, scalar subqueries, and EXISTS filters — with real examples and the performance reasoning behind each choice.
Imagine you're working with an e-commerce database and your manager needs a report: "Show me all customers who placed at least one order over $500 last quarter, along with their total spend and how it compares to the average customer." You know what data you need, but translating that sentence into SQL means layering several pieces of logic on top of each other. Do you write one massive query with everything stuffed into the WHERE clause? Do you create a temporary table? Do you write a subquery inside another subquery until the whole thing looks like a Russian nesting doll?
This is where subquery factoring techniques come in. SQL gives you several tools for embedding logic within a query rather than outside it — derived tables, scalar subqueries, and EXISTS filters. Each one solves a different problem, and knowing which to reach for is the difference between a query that's elegant and fast versus one that's slow, brittle, and impossible to read six months later. If you've already explored subqueries and correlated subqueries, this lesson picks up where that leaves off, going deeper into practical patterns for structuring complex filtering and aggregation logic.
By the end of this lesson, you'll be writing multi-layered queries with confidence — choosing the right subquery pattern for the job and understanding why each pattern behaves the way it does at runtime.
What you'll learn:
EXISTS and NOT EXISTS filter rows efficiently without joiningYou should be comfortable with basic SELECT, WHERE, GROUP BY, and JOIN syntax. Familiarity with aggregate functions like SUM(), COUNT(), and AVG() is assumed. If you've seen a subquery before — even a simple one in a WHERE clause — you're ready for this lesson.
Before we get into the different flavors, let's anchor the mental model. A subquery is simply a SELECT statement nested inside another SQL statement. SQL processes the innermost query first, then uses its result to satisfy the outer query. Think of it like math: (3 + 4) * 2 — the parentheses tell the evaluator what to resolve first.
There are three main ways a subquery can appear:
FROM clause — as a derived table (also called an inline view)SELECT clause — as a scalar subquery returning a single valueWHERE clause — as a filter, often using IN, EXISTS, or a comparison operatorEach location implies different behavior and has different performance characteristics. Let's walk through them one by one with real examples.
A derived table is a subquery that appears in the FROM clause of your outer query. The database engine runs it, produces a temporary result set, and then your outer query treats that result set exactly like a real table.
Here's the canonical use case: you need to filter on an aggregate value. Suppose you want to find all customers whose average order value is above $200. You can't write WHERE AVG(order_total) > 200 directly — aggregate functions can't live in a WHERE clause. You need to compute the aggregate first, then filter on it.
SELECT
customer_summary.customer_id,
customer_summary.customer_name,
customer_summary.avg_order_value
FROM (
SELECT
c.customer_id,
c.customer_name,
AVG(o.order_total) AS avg_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
) AS customer_summary
WHERE customer_summary.avg_order_value > 200;
The inner query (customer_summary) aggregates orders by customer. The outer query simply filters that result. Notice the AS customer_summary alias — this is required in most databases; you can't leave a derived table unnamed.
Key insight: Derived tables let you treat the output of any
SELECT— including one withGROUP BYandHAVING— as though it were a plain table. This unlocks a whole class of filtering that's impossible in a singleWHEREclause.
If you've used Common Table Expressions (CTEs), you've seen that a WITH clause solves the same problem as a derived table in many cases. The core difference is readability and reuse:
Tip: Use a derived table when you have a single, localized use — a quick aggregate filter you need once. Switch to a CTE when the same subquery result is referenced more than once, or when nesting gets deeper than two levels. Readability is a real cost.
You can nest derived tables inside derived tables, but resist the urge to go more than one level deep without asking yourself whether a CTE would be clearer. Deeply nested inline views are one of the top readability anti-patterns covered in Advanced SQL Anti-Patterns.
-- Three levels deep. Don't do this without a very good reason.
SELECT *
FROM (
SELECT *
FROM (
SELECT *
FROM orders
WHERE status = 'completed'
) completed_orders
WHERE order_total > 100
) high_value_completed
WHERE customer_id IN (SELECT customer_id FROM vip_customers);
Each of those layers could be a named CTE. The logic would be identical, but your future self would thank you.
A scalar subquery is a subquery in the SELECT clause that returns exactly one value — one row, one column. That value gets included in each row of your result set.
This is particularly useful when you want to add context values — like a category average, a company-wide total, or the most recent event date — alongside each row, without doing a full JOIN and potentially multiplying rows.
Suppose you want to show each product's price alongside the average price across the entire catalog:
SELECT
product_name,
price,
(SELECT AVG(price) FROM products) AS catalog_avg_price,
price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;
Each row gets the same catalog average attached to it. This is clean and readable. However, there's a trap: if your database doesn't optimize this, it may execute the inner query once per row.
Warning: Scalar subqueries that reference the outer query (called correlated scalar subqueries) are evaluated once per row and can devastate performance on large tables. A subquery like
(SELECT MAX(order_date) FROM orders WHERE orders.customer_id = c.customer_id)runs for every single customer row. On a table with 500,000 customers, that's 500,000 separate subquery executions. Always check your query plan withEXPLAIN ANALYZE. For guidance on reading those plans, see Query Profiling and Statistics in SQL.
Correlated scalar subqueries aren't always bad — modern optimizers in PostgreSQL, SQL Server, and Oracle often convert them to efficient joins or hash operations automatically. But you should know when they're justified:
JOIN would cause row duplication and you'd need DISTINCT or GROUP BY to clean it upA better alternative for large tables is often a LEFT JOIN to a pre-aggregated subquery:
-- Instead of a correlated scalar subquery:
SELECT
c.customer_id,
c.customer_name,
recent.last_order_date
FROM customers c
LEFT JOIN (
SELECT customer_id, MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
) recent ON c.customer_id = recent.customer_id;
This runs the inner query once, then joins — far more efficient than a correlated scalar subquery running per row.
EXISTS is one of SQL's most underused tools. It answers a simple question: does at least one row satisfying this condition exist? It doesn't care how many rows match, what columns they have, or any values — just whether any qualifying row is there.
The syntax looks like this:
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_total > 500
AND o.order_date >= '2024-01-01'
);
Notice SELECT 1 inside the subquery. You'll often see SELECT * or SELECT 1 — it doesn't matter what you select, because EXISTS only cares whether any row is returned at all. Using SELECT 1 is a convention that signals intent: "I don't want data from this subquery, I just want to know if anything exists."
Key insight:
EXISTSshort-circuits. Once the database finds a single qualifying row in the subquery, it stops looking. This makes it highly efficient when the matching rows are indexed — the optimizer can find one match quickly and move on, rather than scanning the entire set.
IN is another common approach to the same problem:
-- Using IN
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_total > 500
AND order_date >= '2024-01-01'
);
For many workloads and modern databases, the optimizer treats these identically. But there are practical reasons to prefer EXISTS:
| Scenario | Prefer |
|---|---|
Subquery could return NULL values |
EXISTS (safer — IN with NULLs behaves unexpectedly) |
| Matching set is very large | EXISTS (short-circuits early) |
| You need to use multiple columns to match | EXISTS (simpler to express correlated conditions) |
| Simple single-column lookup, small set | IN (often more readable) |
The NULL behavior is the critical one. If the subquery in an IN clause returns any NULL values, no rows will match — because SQL's three-valued logic means x IN (1, 2, NULL) evaluates to UNKNOWN for values that don't match 1 or 2. EXISTS sidesteps this entirely.
NOT EXISTS answers the opposite question: are there zero rows matching this condition? It's the SQL equivalent of "show me everything without a counterpart."
-- Find customers who have NEVER placed an order
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
This is sometimes called an anti-join pattern. You could achieve the same result with a LEFT JOIN ... WHERE o.customer_id IS NULL, but NOT EXISTS is often more readable and semantically explicit. The Advanced JOIN Patterns article covers the join equivalent in depth if you want to compare both approaches.
Tip: Prefer
NOT EXISTSoverNOT INwhen the subquery could containNULLvalues.NOT IN (1, 2, NULL)will never return true for any value — a silent, devastating bug.NOT EXISTSdoesn't have this problem.
Let's put everything together with a realistic scenario. You're an analyst at a SaaS company. Your task: find all accounts that:
This requires derived tables for the median calculation, EXISTS for the login check, and NOT EXISTS for the support ticket check.
SELECT
a.account_id,
a.account_name,
a.contract_value,
median_data.median_contract_value,
a.contract_value - median_data.median_contract_value AS value_above_median
FROM accounts a
-- Derive the median once and cross join to every row
CROSS JOIN (
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY contract_value)
AS median_contract_value
FROM accounts
WHERE status = 'active'
) AS median_data
WHERE
-- Condition 1: Active recently
EXISTS (
SELECT 1
FROM user_sessions us
WHERE us.account_id = a.account_id
AND us.login_timestamp >= NOW() - INTERVAL '30 days'
)
-- Condition 2: Above-median contract value
AND a.contract_value > median_data.median_contract_value
-- Condition 3: No critical support tickets recently
AND NOT EXISTS (
SELECT 1
FROM support_tickets st
WHERE st.account_id = a.account_id
AND st.severity = 'critical'
AND st.created_at >= NOW() - INTERVAL '90 days'
)
ORDER BY a.contract_value DESC;
Walk through what's happening:
CROSS JOIN with the derived table median_data computes the median once and makes it available to every row — this is far more efficient than a correlated scalar subquery that would recompute the median for every account.EXISTS conditions filter cleanly and independently. Each one short-circuits, and each one can use an index on account_id plus the date column.WHERE clause corresponds to one business rule, clearly commented.Note:
PERCENTILE_CONTis a SQL standard aggregate function for computing medians. Not all databases support it identically — for a deeper look at statistical aggregates, see Statistical Aggregations in SQL.
Use the following schema (or create equivalent tables in your preferred database):
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10,2)
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
line_total DECIMAL(10,2)
);
CREATE TABLE product_reviews (
review_id INT PRIMARY KEY,
product_id INT,
rating INT, -- 1 to 5
review_date DATE
);
Exercise 1: Write a query using a derived table to find all products whose average line_total across all orders is greater than $150. Return the product name, category, and average line total.
Exercise 2: Add a scalar subquery to that result that shows the overall average line total across all products alongside each row, so you can see how each product compares.
Exercise 3: Extend the query with an EXISTS condition so it only includes products that have received at least one review with a rating of 4 or 5 in the last 12 months.
Exercise 4 (Challenge): Add a NOT EXISTS condition so products with any review of rating 1 or 2 in the last 12 months are excluded — even if they also have good reviews.
For bonus points, rewrite the same query using CTEs instead of derived tables and compare the readability.
Most databases require a derived table to have an alias. Forgetting it produces an error like "Every derived table must have its own alias" (MySQL) or "subquery in FROM must have an alias" (PostgreSQL).
-- WRONG
SELECT * FROM (SELECT customer_id FROM orders GROUP BY customer_id);
-- CORRECT
SELECT * FROM (SELECT customer_id FROM orders GROUP BY customer_id) AS cust_ids;
As mentioned earlier, NOT IN (subquery) returns no rows if the subquery produces any NULL value. This is a silent bug — you get an empty result instead of an error.
-- Dangerous if orders.referral_code has NULLs
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT referral_code FROM orders);
-- Safe
SELECT * FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.referral_code = c.customer_id
);
A scalar subquery in SELECT must return exactly one row. If your subquery can return multiple rows, you'll get a runtime error. Use MAX(), MIN(), or LIMIT 1 to guarantee a single value, but make sure that's actually the right business logic.
-- Will error if a customer has multiple account managers
SELECT
c.customer_name,
(SELECT manager_name FROM account_assignments aa
WHERE aa.customer_id = c.customer_id) AS manager -- might return 2+ rows!
FROM customers c;
-- Fixed
SELECT
c.customer_name,
(SELECT manager_name FROM account_assignments aa
WHERE aa.customer_id = c.customer_id
ORDER BY assigned_date DESC LIMIT 1) AS manager
FROM customers c;
EXISTS with a properly indexed column is fast. But if the column being checked isn't indexed, the database scans the entire table for every outer row. Always ensure that the columns used in EXISTS correlations — typically foreign keys — have indexes. For a systematic approach to indexing decisions, see Indexing Fundamentals for Query Performance.
Warning:
EXISTSis not magic. It short-circuits the row count, but if finding even one matching row requires a full table scan because no index exists, you're still paying that cost for every row in the outer query. Index your join keys.
Let's solidify what you've learned:
Derived tables (inline views in the FROM clause) let you filter and aggregate in layers — solving the common problem of filtering on aggregate results without a separate step. They're great for single-use logic; graduate to CTEs when readability or reuse demands it.
Scalar subqueries in the SELECT clause embed computed values row-by-row. They're elegant for adding context columns, but watch for correlated versions running once per row. Often a LEFT JOIN to a pre-aggregated derived table is the more scalable alternative.
EXISTS and NOT EXISTS are your go-to tools for presence/absence filtering. They short-circuit efficiently, handle NULL safely (unlike NOT IN), and keep business logic readable by isolating each filter condition.
The underlying theme is intentional structure: each technique gives you a way to stage your query's logic so each piece can be understood and verified independently. A well-factored query reads almost like a specification: "Get accounts that exist in the active sessions list and whose value exceeds the median and that don't appear in the critical tickets list."
Where to go next:
EXPLAIN output will make you a dramatically better troubleshooter.