Subqueries

A subquery is a SELECT inside another statement. It lets you answer a question in steps: first find the average salary, then find who earns more than it. Interviewers ask for one in almost every SQL round, and the follow-ups test whether you know the different kinds and the NULL trap. In every example below, the query in brackets is the inner query, and the query around it is the outer query. The examples use the company data.

A subquery that returns one value

A scalar subquery returns a single value, so it can stand wherever a value can. "Who earns more than the average?":

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

name | salary
Asha Rao | 150000
Vikram Shah | 95000
Neha Gupta | 82000
Dev Malhotra | 95000

The inner query runs first and gives 77,700, the average salary. The outer query then compares each salary with it. If a scalar subquery returns more than one row, MySQL and PostgreSQL raise an error, while SQLite quietly uses the first row. An aggregate such as AVG or MAX guarantees one value.

IN with a subquery

A subquery that returns one column of many rows can feed IN. "Which employees are the rep on a delivered order?":

SELECT name FROM employees
WHERE id IN (SELECT employee_id FROM orders WHERE status = 'DELIVERED');

name
Arjun Das
Kavya Iyer
Imran Khan

Kavya Iyer has two delivered orders but appears once. A join would return her twice unless you add DISTINCT. So when you only need to know whether a match exists, IN or EXISTS is the cleaner choice.

EXISTS and correlated subqueries

A correlated subquery uses a column of the outer query, so logically it runs once for each outer row. EXISTS is true as soon as the subquery finds any row. What it selects does not matter, which is why people write SELECT 1. "Who manages at least one person?":

SELECT m.name FROM employees m
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.manager_id = m.id);

name
Asha Rao
Vikram Shah
Neha Gupta
Arjun Das

Correlated subqueries also compare a row with its own group. "Who earns more than the average of their own department?":

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

name | dept_id | salary
Asha Rao | 1 | 150000
Neha Gupta | 2 | 82000
Arjun Das | 3 | 70000

Imran Khan is alone in his department, so his salary equals its average and is not "more than" it. Sneha Patil has no department. For her, x.dept_id = NULL matches nothing, the average is NULL, the comparison is unknown, and she drops out too.

NOT IN versus NOT EXISTS

For "who manages nobody", NOT IN looks natural. But the manager_id column contains a NULL, and NOT IN then returns no rows at all (the NULL topic shows why). Either remove the NULLs from the list or use NOT EXISTS:

SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

name
Rohan Mehta
Priya Nair
Kavya Iyer
Imran Khan
Sneha Patil
Dev Malhotra

Subqueries in FROM and the WITH clause

A subquery in FROM, called a derived table, builds a temporary result that you then query like a table. Give it an alias: MySQL and PostgreSQL before version 16 reject a derived table without one (SQLite does not need it). "Which departments have a salary total above the average department total?" needs two levels: totals per department, then the average of those totals.

SELECT dept_id, total
FROM (SELECT dept_id, SUM(salary) AS total
      FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id) t
WHERE total > (SELECT AVG(total)
               FROM (SELECT SUM(salary) AS total
                     FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id) u);

dept_id | total
1 | 340000
2 | 210000

The repeated part is easier to read with WITH, which gives a temporary result a name once. WITH (a common table expression, or CTE) works in PostgreSQL, SQLite and MySQL 8.0 or later:

WITH totals AS (
  SELECT dept_id, SUM(salary) AS total
  FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id
)
SELECT dept_id, total FROM totals
WHERE total > (SELECT AVG(total) FROM totals);

dept_id | total
1 | 340000
2 | 210000

Subquery or join?

Both are correct tools. Use a join when you need columns from both tables in the result. Use IN or EXISTS when you only filter on whether a related row exists, because they never create duplicate rows. Databases often turn one form into the other internally, so write the clearer one first and measure only if it is slow.

How to answer in an interview

Name the three places a subquery can go: in WHERE (one value or a list), in FROM (a derived table) and in SELECT (one value per row). Explain that a subquery is correlated when it uses a column of the outer query. Then mention the NOT IN and NULL trap and say you prefer NOT EXISTS for "has no match" questions.

Common mistakes

• A scalar subquery that can return several rows.
• NOT IN over a column that may hold NULL.
• Forgetting the alias on a derived table.
• Joining when you only need "exists", then getting duplicate rows.

Recap

A subquery is a query inside a query. Use a scalar subquery for one value, IN or EXISTS for "has a match", NOT EXISTS for "has no match", and FROM or WITH for results built in steps.