Subqueries · SQL & Databases

A subquery is a query inside a query. It answers questions in two steps — compute something, then use it to filter or compare.

A subquery (inner query) is a SELECT nested inside another statement. It lets you use the result of one query within another — to compare against a computed value, test membership, or check existence. Subqueries appear in WHERE, in SELECT, and in FROM (as derived tables).

Scalar, IN/EXISTS, and correlated

A scalar subquery returns a single value to compare against. IN tests membership in a returned list; EXISTS tests whether any matching row exists. A correlated subquery references the outer query and runs once per outer row.

Employees earning above the company average

SELECT name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);   -- scalar subquery

IN and EXISTS

-- employees in departments located in Bengaluru
SELECT name FROM employees
WHERE  dept_id IN (SELECT dept_id FROM departments WHERE location = 'Bengaluru');

-- departments that have at least one project (EXISTS, correlated)
SELECT d.dept_name FROM departments d
WHERE  EXISTS (SELECT 1 FROM projects p WHERE p.dept_id = d.dept_id);

⚡ The edge

A scalar subquery must return exactly one value; if it can return many, use IN/EXISTS instead. EXISTS stops at the first match, so it's often faster than IN for large or correlated checks.
Beware NOT IN with NULLs: if the subquery returns any NULL, NOT IN yields no rows. Prefer NOT EXISTS, which handles NULLs correctly.

Worked example

Find employees who earn more than the average salary of their own department.

This needs a per-department average, compared to each employee — a correlated subquery.
For each outer employee row, the subquery computes AVG(salary) for that employee's department.
Keep the employee if their salary exceeds that department average.

Answer: SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees x WHERE x.dept_id=e.dept_id);

Worked example

List employees who are not assigned to any project (assume an assignments table).

You want employees with no matching assignment row.
NOT EXISTS with a correlated check is the safe pattern (NULL-proof).
Return employees where no assignment row references their emp_id.

Answer: SELECT name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM assignments a WHERE a.emp_id=e.emp_id);

⚠ Watch out

A scalar subquery returning more than one row errors — use IN/EXISTS for multiple values.
NOT IN (... NULL ...) returns no rows; use NOT EXISTS instead.
Correlated subqueries run per outer row — fine logically, but can be slow; a join may be faster.