Introduction
A subquery (or inner query) is a SELECT statement embedded inside another SQL statement. It runs first, and its result is used by the outer query as a value, a list of values, or a temporary table. Subqueries let you break a complex problem into smaller, composable pieces without needing a join in every case, and they can appear in the SELECT list, the FROM clause (as a derived table), or the WHERE/HAVING clause as a filter condition.
Cricket analogy: Before Virat Kohli's strike rate is compared to the team, the scorer first calculates the tournament's average strike rate as an inner step, then feeds that single number into the outer comparison used in the final report.
Syntax
-- Subquery in WHERE clause
SELECT column1, column2
FROM table1
WHERE column1 = (SELECT MAX(column1) FROM table1);
-- Subquery in FROM clause (derived table)
SELECT sub.dept_id, sub.avg_salary
FROM (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
) AS sub
WHERE sub.avg_salary > 60000;
-- Subquery with IN / NOT IN / EXISTS
SELECT name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'NY');Explanation
A subquery used in WHERE with a single-row result must be compared with a scalar operator (=, >, <). A multi-row subquery must be used with IN, ANY, ALL, or EXISTS instead of =. A subquery placed in FROM is called a derived table and must be given an alias; the outer query treats it exactly like a real table. Unlike a correlated subquery, a plain (non-correlated) subquery is completely independent of the outer query and is evaluated only once, regardless of how many rows the outer query touches.
Cricket analogy: A single strike-rate number gets compared with '>', but a list of every batsman's score needs 'IN' or 'ANY'; a temporary "top scorers" table built mid-analysis must be named, and an independent season average is computed only once, not per player.
Example
-- Find employees earning more than the company average salary
SELECT emp_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Find departments with no employees using NOT EXISTS
SELECT d.dept_id, d.dept_name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);Output
The first query returns only rows whose salary exceeds the single scalar value produced by the inner AVG(salary) query. The second query returns each department row exactly once, for departments where the correlated existence check finds zero matching employee rows -- note that although this example uses a correlated reference (d.dept_id), the overall pattern illustrates how a subquery's result feeds directly into the outer query's filter or projection logic.
Cricket analogy: One query lists batsmen scoring above the tournament's single average score, while a second lists every team for which the correlated check finds zero centuries scored by t.team_id's players -- one team row per empty result.
Key Takeaways
- A subquery is a SELECT nested inside another SQL statement, evaluated to produce a scalar, a list, or a table.
- Scalar subqueries must be compared with =, >, < etc.; multi-row subqueries need IN, ANY, ALL, or EXISTS.
- A subquery in the FROM clause (derived table) must have an alias.
- A non-correlated subquery runs once and is independent of the outer query, unlike a correlated subquery.
- Subqueries can often be rewritten as JOINs; the optimizer's plan and readability should guide the choice.
Practice what you learned
1. Which clause CANNOT contain a subquery in standard SQL?
2. A subquery used in the FROM clause is commonly known as a:
3. What happens if you compare a column to a subquery using '=' but the subquery returns more than one row?
4. How many times is a simple, non-correlated subquery in a WHERE clause typically evaluated?
Was this page helpful?
You May Also Like
Correlated Subqueries
A subquery that references columns from the outer query and is re-evaluated once for every row processed by the outer query.
Common Table Expressions (CTEs)
A named, temporary result set defined with WITH that improves readability and enables recursive queries.
INNER JOIN
Combine rows from two tables where the join condition matches in both, discarding unmatched rows.