Subquery
SQL · SQL Subqueries
Theory
A subquery is a SELECT written inside another statement, in brackets. The inner query runs and its result feeds the outer query. WHERE salary > (SELECT AVG(salary) FROM employees) first works out the average salary, ₹63,000 for the table below, and then keeps employees earning more than that.
Subqueries come in three shapes. A scalar subquery returns one value and can be used anywhere a single value fits, like in SELECT or with = and >. A list subquery returns one column of many rows, used with IN, ANY, ALL or EXISTS. A table subquery returns rows and columns and sits in FROM as a derived table, which needs an alias. A scalar subquery that returns more than one row raises an error. A non-correlated subquery does not depend on the outer row, so it can be evaluated just once.
Why we use it
Many questions depend on another result first: above-average earners, customers in the top city, products never ordered. Subqueries let you compute that intermediate answer inside the same query.
When to use it
Use subqueries when one step's result feeds another, especially for comparisons with aggregates. For multi-step logic, CTEs (WITH) are often easier to read. When you need columns from both tables, a JOIN is usually better.
How it works
- Outer query needs a value or list
- Run the inner query in brackets
- Single value returned? Yes: Use it as a scalar in the comparison. No: Use it as a list or table.
- Outer query filters or joins using that result
- Return the outer query's rows
Syntax
SELECT columns FROM table
WHERE column = (SELECT ... FROM ...);Example
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);Key points
- Can appear in SELECT, FROM, WHERE and HAVING
- Executed once when it does not depend on the outer query
- A scalar subquery must return at most one row
- A subquery in FROM (derived table) needs an alias
- CTEs (WITH) are a more readable way to chain steps