Subqueries in SQL

A subquery is simply a query inside another query. When the answer you want depends on the result of a first calculation — "which employees earn more than the company average?" — you compute that first calculation as a subquery and let the outer query use it. It is one of the ideas that separates comfortable SQL users from beginners.

This tutorial assumes you can already filter, join and group. If aggregate functions like AVG are new to you, read GROUP BY in SQL first, since subqueries lean on them heavily.

Our sample data

-- employees
emp_id | emp_name | department_id | salary
-------+----------+---------------+--------
101    | Aarti    | 1             | 78000
102    | Bhaskar  | 1             | 65000
103    | Chitra   | 2             | 52000
104    | Devan    | 2             | 58000
105    | Esha     | 3             | 47000
106    | Farhan   | 4             | 90000

-- departments
department_id | department_name | location
--------------+-----------------+-----------
1             | Engineering     | Hyderabad
2             | Sales           | Bengaluru
3             | Marketing       | Hyderabad
4             | Finance         | Pune

Scalar subqueries: one value

The simplest subquery returns a single value, which the outer query uses like a constant. To find everyone paid above the company average:

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

The inner query (SELECT AVG(salary) FROM employees) computes one number — the average across all six rows — and the outer query compares each employee's salary against it. You could not write this with a plain WHERE because the average is not a value you know in advance; it must be calculated from the same table.

A scalar subquery can also appear in the SELECT list, producing a computed column:

SELECT emp_name,
       salary,
       salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

Common mistake: Using a scalar subquery that accidentally returns more than one row. If (SELECT salary FROM employees WHERE department_id = 1) returns two rows, the database raises an error because the outer comparison expects a single value. Add an aggregate like MAX or a LIMIT 1, or switch to IN.

IN subqueries: membership in a set

When the subquery returns a list of values, use IN to test membership. To list every employee who works in a Hyderabad-based department:

SELECT emp_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Hyderabad'
);

The inner query returns the ids of Hyderabad departments (1 and 3), and the outer query keeps employees whose department_id is in that set. NOT IN inverts it — everyone not in a Hyderabad department.

Pro tip: Be careful with NOT IN when the subquery can produce NULLs. If any value in the list is NULL, NOT IN returns no rows at all because of SQL's three-valued logic. When NULLs are possible, prefer NOT EXISTS, which handles them cleanly.

EXISTS subqueries: does any row match?

EXISTS does not care about values — it checks whether the subquery returns any row. It shines with correlated subqueries. To find departments that actually have at least one employee:

SELECT d.department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

This drops Marketing, because no employee points to department 3. The SELECT 1 is idiomatic — with EXISTS, the columns the subquery selects are irrelevant, so developers write a constant to signal "we only care whether a row exists."

Correlated subqueries: one run per outer row

The EXISTS example above is a correlated subquery: it references d.department_id from the outer query, so it cannot run independently. The database evaluates it once for each outer row, plugging in that row's value.

A classic correlated pattern finds employees who earn the most in their own department:

SELECT e.emp_name, e.salary, e.department_id
FROM employees e
WHERE e.salary = (
    SELECT MAX(m.salary)
    FROM employees m
    WHERE m.department_id = e.department_id
);

For each employee e, the inner query computes the maximum salary within that employee's department and keeps the row only if it matches. Correlated subqueries are powerful but re-execute per row, so on large tables they can be slower than an equivalent join or window function.

The interview favorite: second-highest salary

No subquery topic is complete without the second-highest-salary question, which appears in a huge share of SQL interviews. One clean subquery approach:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

The inner query finds the top salary; the outer query finds the largest salary strictly below it. It reads almost like the English description. There are several other approaches using LIMIT/OFFSET and window functions — the full set is walked through in how to find the second highest salary in SQL.

Interview note: When asked for the Nth-highest salary, be ready with at least two methods and mention the edge case of duplicate salaries. Interviewers often follow up with "what if two people share the top salary?" — using DISTINCT inside the subquery handles it.

ANY and ALL: comparing against a set

ANY and ALL let a comparison operator work against a whole list from a subquery. > ALL means "greater than every value," and > ANY means "greater than at least one value."

-- employees paid more than EVERYONE in department 2
SELECT emp_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department_id = 2
);

-- employees paid more than SOMEONE in department 2
SELECT emp_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE department_id = 2
);

> ALL (subquery) is equivalent to comparing against the maximum of the set, and > ANY is equivalent to comparing against the minimum. Many developers find the MAX/MIN scalar form clearer, but recognizing ANY and ALL matters because interviewers and legacy code use them.

Subqueries in the FROM clause

A subquery can also stand in for a table in FROM, called a derived table or inline view. This is handy when you need to group first and then filter or join on the grouped result:

SELECT dept_avg.department_id, dept_avg.avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg
WHERE dept_avg.avg_salary > 55000;

The inner query builds a per-department average table on the fly, and the outer query filters it. Every derived table must be given an alias — here dept_avg — or the database rejects the query.

Subquery or join?

A frequent question is when to use a subquery versus a join. A practical guide:

Many subqueries can be rewritten as joins and vice versa, often with identical performance. Choose the form that expresses the question most clearly, and let readability guide you until performance data says otherwise.

Where to go next

Subqueries let your queries depend on the results of other queries — a leap in expressive power. Scalar, IN, EXISTS and correlated forms together cover most real-world needs.

For ranking and running-total problems where subqueries get awkward, the cleaner tool is a window function; continue with SQL window functions introduction. To pressure-test what you have learned, work through the SQL interview questions for freshers, where subquery problems appear often.