What is a Subquery?
Learn what a SQL subquery is, the difference between scalar, correlated, and table-valued subqueries, and when to use a JOIN or CTE instead.
Expected Interview Answer
A subquery is a SELECT query nested inside another SQL statement, used to produce a value, a list of values, or a row set that the outer query then filters, compares against, or selects from, evaluated either once or once per outer row depending on whether it is correlated.
A scalar subquery returns a single value and can be used anywhere an expression is expected, such as in SELECT or WHERE. A subquery returning multiple rows is typically used with IN, ANY, or ALL, while a table-valued subquery appears in the FROM clause and is treated like a derived table. A correlated subquery references a column from the outer query, so the database logically re-executes it for every outer row, which can be expensive on large tables; an uncorrelated subquery runs independently and only once. Modern query optimizers often rewrite correlated subqueries into equivalent JOINs internally, but for readability and sometimes for performance, an explicit JOIN or a CTE is frequently preferred over a subquery when the same result can be achieved.
- Lets you filter based on aggregated or computed values from another query
- Breaks complex logic into a nested, self-contained step
- Correlated subqueries enable per-row comparisons like 'greater than this row's group average'
- Table-valued subqueries let you treat a query result as a temporary table
AI Mentor Explanation
A subquery is like a selector first calculating the team's average score in a side calculation, then using that number to pick only batsmen who scored above it. The inner calculation runs first and hands its result to the outer selection process, which could not proceed without it.
How a scalar subquery feeds a value into the outer query
Inner subquery
- SELECT AVG(amount) FROM orders
- returns one scalar value
Outer query
- SELECT * FROM orders
- WHERE amount > (subquery result)
Step-by-Step Explanation
Step 1
Identify what value you need first
Determine an intermediate value, list, or row set the outer query depends on, such as an average or a set of IDs.
Step 2
Write the inner query
Build a standalone SELECT statement that produces exactly that value, list, or row set.
Step 3
Place it correctly
Put a scalar subquery in SELECT/WHERE, a list subquery with IN/ANY/ALL, or a table subquery in FROM.
Step 4
Decide correlated vs uncorrelated
Reference an outer column inside the subquery only if you need per-row comparisons; otherwise keep it independent for a single evaluation.
Step 5
Test the inner query alone
Run the subquery by itself first to confirm it returns the expected value or rows.
Step 6
Check the execution plan
Use EXPLAIN to see whether the optimizer rewrites the subquery into a join and whether performance is acceptable.
What Interviewer Expects
- Defines a subquery as a nested SELECT inside another query
- Distinguishes scalar, row, and table-valued subqueries
- Explains the difference between correlated and uncorrelated subqueries
- Knows subqueries can appear in SELECT, WHERE, FROM, and HAVING
- Can discuss when a JOIN or CTE might be preferable to a subquery
Common Mistakes
- Using a subquery expected to return one row with = instead of IN, causing an error on multiple rows
- Writing a correlated subquery that re-runs per outer row and tanking performance on large tables
- Not aliasing a derived table subquery in FROM, which some databases require
- Assuming a subquery is always slower or always faster than a JOIN without checking the plan
Best Answer (HR Friendly)
“A subquery is a query inside another query, kind of like doing a calculation first and then using that result to answer the bigger question. For example, you might first find the average order size, then use that number to list only the orders that are bigger than average.”
Code Example
-- orders: id, customer_id, amount
-- (1,1,100), (2,1,300), (3,2,500), (4,2,50)
-- Uncorrelated scalar subquery: compare to overall average
SELECT id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
-- overall avg = 237.5 -> returns orders 2 (300) and 3 (500)
-- Correlated subquery: compare to each customer's own average
SELECT o.id, o.customer_id, o.amount
FROM orders o
WHERE o.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.customer_id = o.customer_id
);
-- customer 1 avg = 200 -> order 2 (300) qualifies
-- customer 2 avg = 275 -> order 3 (500) qualifiesFollow-up Questions
- What is the difference between a correlated and an uncorrelated subquery?
- When would you use a CTE instead of a subquery?
- What is the difference between EXISTS and IN with a subquery?
- Can a subquery in FROM be joined like a regular table?
- How does the query optimizer typically handle correlated subqueries?
MCQ Practice
1. What is a scalar subquery?
A scalar subquery returns a single value, so it can be used anywhere a single expression is expected, such as in SELECT or WHERE.
2. What distinguishes a correlated subquery from an uncorrelated one?
A correlated subquery references an outer query column, so it is logically re-evaluated for each outer row, unlike an uncorrelated subquery which runs independently.
3. Where can a table-valued subquery legally appear in a SELECT statement?
A subquery placed in the FROM clause is treated as a derived table that the outer query can select from or join to.
Flash Cards
What is a subquery? — A SELECT query nested inside another SQL statement, used to produce a value, list, or row set for the outer query.
What is a correlated subquery? — A subquery that references a column from the outer query, causing it to logically re-run for each outer row.
What is a scalar subquery used for? — Returning a single value that can be used as an expression, e.g. comparing a column against an average.
Where can a subquery appear in a query? — In SELECT, WHERE, HAVING (as a value or condition), or FROM (as a derived table).