intermediateCore SQL100% Free

SQL Subqueries

Definition

A SQL subquery is a query nested inside another SQL statement, used to compute values, filter rows, or define temporary datasets for the outer query.

Introduction to SQL Subqueries

Subqueries let you use the result of one query as input to another. They can appear in SELECT, FROM, WHERE, and HAVING clauses. While CTEs are often more readable for complex logic, subqueries are compact and powerful for embedding one-off calculations or filters directly in a query.

Syntax

SQL
-- Subquery in WHERE
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in FROM (derived table)
SELECT dept, avg_sal
FROM (SELECT department AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY department) sub;

Examples

1

Scalar Subquery — Single Value

SELECT name, salary,
  salary - (SELECT AVG(salary) FROM employees) AS vs_avg
FROM employees
ORDER BY vs_avg DESC;

The subquery computes the company average once. Each row shows how much above or below average the employee's salary is.

Try in Playground
2

Correlated Subquery

SELECT name, department, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department  -- references outer query
);

A correlated subquery references the outer query (e.department). It is re-executed for each outer row, returning employees who earn above their own department's average.

Try in Playground
3

IN with Subquery

SELECT name FROM employees
WHERE dept_id IN (
  SELECT id FROM departments
  WHERE budget > 1000000
);

The subquery returns a list of high-budget department IDs, and the outer query finds all employees in those departments.

Try in Playground

Common Mistakes

Using correlated subqueries on large tables — executes N times (once per outer row), very slow

Subquery returns multiple rows when scalar expected — causes runtime error

Forgetting to alias derived tables: FROM (SELECT ...) — the alias is required in most databases

Using subqueries where a JOIN would be more efficient and readable

Frequently Asked Questions

What is a subquery in SQL?
A subquery is a SELECT statement nested inside another SQL statement. It can appear in WHERE (to filter), FROM (as a derived table), SELECT (as a scalar value), or HAVING. The inner query runs first, and its result is used by the outer query.
What is a correlated subquery?
A correlated subquery references columns from the outer query. This means it is re-executed for each row of the outer query, not just once. Example: SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept) — the subquery uses e.dept from the outer row.
When should I use a subquery vs JOIN?
Use JOINs when you need columns from both tables in the result. Use subqueries when you only need data from one table but need to filter based on another table's data. For performance, JOINs are usually faster than correlated subqueries on large datasets.
What is a derived table in SQL?
A derived table is a subquery in the FROM clause that acts as a virtual table: SELECT * FROM (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) AS dept_stats. The alias (dept_stats) is required.

Related SQL Topics

JoinsCte Common Table ExpressionsWindow FunctionsWhere ClauseQuery Optimization

Practice This in the SQL Playground

Write real queries, see live results, and master SQL Subqueries hands-on. 100% free.

Open SQL PlaygroundBrowse All Lessons