Subqueries

A subquery is a SELECT statement nested inside another SQL statement. The outer query uses the subquery's result as if it were a value, a list, or a table. The database executes the subquery first, then uses the result to evaluate the outer query.

Four types based on where the subquery appears and what it returns. A scalar subquery appears in SELECT, WHERE, or HAVING and returns exactly one row and one column — a single value. It is used to compare a column against a computed aggregate or to add a computed reference value to each row. A table subquery (derived table) appears in the FROM clause and returns multiple rows and columns — a virtual table that the outer query can JOIN, filter, and aggregate. It is essential for pre-aggregation and multi-step logic. A correlated subquery references columns from the outer query, making it dependent on the outer row — it executes once per outer row. It is used for row-level comparisons against group aggregates and for EXISTS/NOT EXISTS existence checks. An IN/NOT IN subquery returns a list of values that the outer WHERE clause filters against.

The choice of subquery type follows from what the query needs: a single comparison value → scalar subquery in WHERE; a list of valid IDs → IN subquery; pre-aggregated data to join → derived table in FROM; per-row group aggregate → correlated subquery (or JOIN to a derived table for performance); existence check → EXISTS correlated subquery.

A non-correlated subquery executes independently of the outer query — it produces a single result (a value, a list, or a table) and the outer query uses that result. The subquery runs once, and the outer query uses the cached result for every row it evaluates. SELECT AVG(salary) FROM employees is non-correlated — it produces one number regardless of which employee row the outer query is examining.

A correlated subquery references a column from the outer query — it cannot be evaluated without knowing the current outer row's values. It executes once per outer row, substituting the current row's values each time. SELECT AVG(e2.salary) FROM employees AS e2 WHERE e2.department = e.department is correlated — it computes the average salary for the department of the current employee being examined by the outer query. For each employee row in the outer query, the subquery reruns with a different department value.

The performance implication is the key distinction: a non-correlated subquery runs once regardless of outer table size. A correlated subquery runs N times for N outer rows. On a table with 10,000 employees, a correlated subquery that computes the department average runs 10,000 times — even though there are only 20 departments. The JOIN equivalent computes the department average once per department (20 times) and joins — 500x less work. Always consider replacing correlated subqueries with JOIN to a pre-aggregated derived table or with window functions for large-table performance.

Use EXISTS when you are checking for the existence of a related record — you need to know whether at least one row satisfying a condition exists in another table, but you do not need the actual values from that table. EXISTS is semantically clearest for existence checks and is always NULL-safe — it never has the NULL-returns-nothing problem that NOT IN has.

EXISTS short-circuits: as soon as one matching row is found, the database stops scanning. For large tables where matches are common, this makes EXISTS significantly faster than IN — IN must evaluate all matching rows and build the complete list before filtering. EXISTS also handles NULLs correctly — NOT EXISTS (subquery with NULLs) still works correctly, whereas NOT IN (subquery with NULLs) silently returns zero rows.

Use IN when you have a small fixed list of values (IN (1, 2, 3) — no subquery), or when the subquery returns a manageable list of values and readability matters more than maximum performance. IN is slightly more readable for simple filtering: WHERE customer_id IN (SELECT customer_id FROM vip_customers) clearly states "get me rows where the customer is in this list." EXISTS requires understanding the correlated reference: WHERE EXISTS (SELECT 1 FROM vip_customers WHERE vip_customers.customer_id = orders.customer_id) is more verbose but semantically equivalent. The practical rule: use EXISTS for large subquery results, nullable columns, and all NOT IN use cases. Use IN for small lists and when you are certain the subquery returns no NULLs.

A derived table is a subquery in the FROM clause — a temporary result set that the outer query treats as a regular table. It is defined inline within the query: FROM (SELECT store_id, SUM(total_amount) AS revenue FROM orders GROUP BY store_id) AS store_revenues. The alias (AS store_revenues) is mandatory — the outer query references it by that name. Derived tables are not stored or cached — they are computed each time the query runs.

A CTE (Common Table Expression) defined with WITH serves the same purpose — it creates a named intermediate result — but it appears before the main query rather than inline. Both are equivalent in terms of what they compute. The difference is primarily readability and reuse. A CTE can be referenced multiple times in the same query; a derived table defined inline can only be used once at the location it is defined.

When to use derived table vs CTE: use an inline derived table for simple, single-use pre-aggregation where the subquery is short and adding a CTE name would be more overhead than clarity. Use a CTE when the same intermediate result is needed more than once (CTE is defined once, referenced by name wherever needed), when the logic has multiple sequential steps that benefit from named intermediate results, or when the derived table subquery is complex enough that naming it adds meaningful documentation. As a practical guide: if a subquery in FROM is more than 5-6 lines, extract it to a named CTE for readability. If it is 2-3 lines, keeping it inline is often cleaner.

This requires comparing each customer's total spend against the average total spend for customers in the same loyalty tier. The tier average is an aggregate that varies by tier — it cannot be computed with a single scalar subquery (which would give the overall average, not the per-tier average).

Approach 1 — correlated subquery: for each customer, run a subquery that computes the average total spend for customers in the same tier. SELECT c.customer_id, c.loyalty_tier, SUM(o.total_amount) AS total_spend FROM customers AS c JOIN orders AS o ON c.customer_id = o.customer_id WHERE o.order_status = 'Delivered' GROUP BY c.customer_id, c.loyalty_tier HAVING SUM(o.total_amount) > (SELECT AVG(tier_spend) FROM (SELECT c2.customer_id, SUM(o2.total_amount) AS tier_spend FROM customers AS c2 JOIN orders AS o2 ON c2.customer_id = o2.customer_id WHERE o2.order_status = 'Delivered' AND c2.loyalty_tier = c.loyalty_tier GROUP BY c2.customer_id) AS tier_totals). This is correct but the correlated subquery in HAVING makes it verbose and potentially slow.

Approach 2 — CTE with JOIN (preferred): WITH customer_totals AS (SELECT c.customer_id, c.loyalty_tier, SUM(o.total_amount) AS total_spend FROM customers AS c JOIN orders AS o ON c.customer_id = o.customer_id WHERE o.order_status = 'Delivered' GROUP BY c.customer_id, c.loyalty_tier), tier_averages AS (SELECT loyalty_tier, AVG(total_spend) AS avg_spend FROM customer_totals GROUP BY loyalty_tier) SELECT ct.customer_id, ct.loyalty_tier, ct.total_spend, ta.avg_spend FROM customer_totals AS ct JOIN tier_averages AS ta ON ct.loyalty_tier = ta.loyalty_tier WHERE ct.total_spend > ta.avg_spend. The CTE approach computes tier averages once (not per customer), is readable, and scales efficiently to large datasets.

Cause: A scalar subquery (used in SELECT, WHERE with =, or HAVING with =) returned multiple rows instead of exactly one. For example, WHERE total_amount = (SELECT total_amount FROM orders WHERE customer_id = 5) — if customer 5 has multiple orders, the subquery returns multiple rows and the = operator fails. A scalar subquery must always return exactly one row.

Fix: Three options: (1) Add an aggregate to force one row: = (SELECT MAX(total_amount) FROM orders WHERE customer_id = 5). (2) Use IN instead of = if multiple matching values are acceptable: WHERE total_amount IN (SELECT total_amount FROM orders WHERE customer_id = 5). (3) Add a LIMIT 1 with ORDER BY to force exactly one row: = (SELECT total_amount FROM orders WHERE customer_id = 5 ORDER BY order_date DESC LIMIT 1). Choose based on which value is semantically correct.

Cause: The NOT IN subquery returns at least one NULL value. NOT IN (1, 2, NULL) is equivalent to col != 1 AND col != 2 AND col != NULL. Since col != NULL evaluates to NULL (not TRUE), the entire AND chain evaluates to NULL for every row — returning zero results. This is the most common and most dangerous subquery NULL trap.

Fix: Add WHERE subquery_column IS NOT NULL inside the subquery: WHERE p.product_id NOT IN (SELECT product_id FROM order_items WHERE product_id IS NOT NULL). Or switch to NOT EXISTS which is NULL-safe: WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id). Or use LEFT JOIN + IS NULL. Before using NOT IN in production, always verify the subquery cannot return NULLs: SELECT COUNT(*) FROM subquery_table WHERE join_column IS NULL.

Cause: The correlated subquery executes once per outer row. With 10,000 outer rows, the subquery runs 10,000 times. If the subquery itself does a full table scan (no index on the correlated column), total work is 10,000 × full_scan_cost. On large tables, this produces O(n²) complexity — exponentially worse than a single-pass JOIN.

Fix: Replace the correlated subquery with a JOIN to a pre-aggregated derived table or CTE. Instead of SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department = e.department per row, compute: (SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department) AS dept_avgs — once per department — and JOIN to it. Also ensure the correlated column (e2.department in the subquery) is indexed. Use EXPLAIN ANALYZE to confirm the rewrite reduces total scans.

Cause: Every subquery used as a derived table (in the FROM clause or as a JOIN target) must have an alias. FROM (SELECT ...) is a syntax error — the alias is mandatory. This is a hard SQL rule: the derived table must be named so the outer query can reference it.

Fix: Add an alias immediately after the closing parenthesis: FROM (SELECT ...) AS my_derived_table. The alias can then be used in JOIN conditions, WHERE clauses, and SELECT column prefixes. Choose a descriptive alias that explains what the derived table contains: AS store_revenues, AS customer_spend, AS top_products — not AS t1 or AS sub.

Cause: The correlated condition in the EXISTS subquery is missing or too broad. EXISTS (SELECT 1 FROM orders) without a WHERE condition that links to the outer query is always TRUE for every outer row — it just checks whether the orders table has any rows at all, not whether a specific customer has orders. This turns the WHERE EXISTS into a no-op filter.

Fix: Always include a correlated condition in EXISTS that links the subquery to the current outer row: WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id). The WHERE inside EXISTS must reference the outer alias (c.customer_id) — without this, EXISTS evaluates once globally, not per row. Test by temporarily changing EXISTS to a SELECT * and verifying the subquery returns the expected rows for a specific outer row value.

Write a single query that produces a product performance report using subqueries. For each product show: product_id, product_name, category, unit_price, total_units_sold (0 if never sold), total_revenue (0 if never sold), a 'vs_category_avg_revenue' column showing how the product's revenue compares to the average revenue of products in the same category that were sold, and a performance_tier: 'Top' if revenue is above category average, 'Average' if within ±20% of category average, 'Below' if more than 20% below, 'Unsold' if never sold. Use a derived table in FROM for sales aggregation and a correlated subquery for the category average.