Subqueries and Nested Queries in SQL

SQL Tutorials

Subqueries and nested queries enable you to include a query within another query in SQL. The inner query executes first and the outer query executes second, using the inner query result. We use subqueries when a single query alone would not be able to solve the problem.

They allow you to filter data, compare values, and work with related tables.

What Is a Subquery?

A subquery is a query nested within another SQL statement.

You can use it inside:

SELECT
WHERE
FROM
HAVING

Inner query is known as nested query.

Basic Example

Suppose you have an employees table:

id	name	salary	department_id

Now you want employees who earn more than the average salary.

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

How this works:

The inner query calculates the average salary.
The outer query then asks for all employees, with salary greater than that value.

Types of Subqueries

1. Subquery in WHERE Clause

This is the most regular.

SELECT name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Delhi'
);

The inner query locates the department ID within Delhi.
After this, the outer query retrieves the employees who are in these departments.

2. Subquery in SELECT Clause

SELECT 
    name,
    (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Now our average salary is repeating in all the rows.

3. Subquery in FROM Clause

SELECT avg_salary
FROM (
    SELECT AVG(salary) AS avg_salary
    FROM employees
) AS salary_table;

In this case, the inner query can be considered as a temporary table.

Single Row vs Multiple Row Subqueries

Single Row Subquery

Returns one value.

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

Multiple Row Subquery

It returns multiple values.

SELECT name
FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments
);

When multiple rows are returned, use operators like:

IN
ANY
ALL

Subqueries with IN

You use IN when the subquery has many values. It compares the result to each value of the inner query.

SELECT name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Delhi'
);

The inner query appears to return the department ID.
The outer query is used to find those departments that employee.

IN is suitable for smaller result sets.

Subqueries with EXISTS

EXISTS checks to see if the subquery returns at least one row. It doesn‘t compare values. It just checks for existence.

SELECT name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND d.location = 'Delhi'
);

And for each employee, it verifies if a department exists that match.

EXISTS performs best when many rows are in the inner query.

IN vs EXISTS Comparison

IN	EXISTS
Compares values	Checks if rows exist
Works well for small lists	Works well for large data
May slow down with large results	Often faster with large data
Returns based on matching values	Returns when condition is true

Both can give the same result. However, performance may differ depending on data size and indexes.

Correlated Subqueries

A correlated subquery relies on the outer query. It executes once for each row.

SELECT e1.name
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

In this case, the inner query uses a value fetched from the outer query. So, as a consequence, it is executed many times.

Correlated subqueries are useful but can be slow.

Subquery vs JOIN

Sometimes you can replace a subquery with a JOIN.

Use subqueries when:

You want simple filtering logic
You need step by step conditions

Use joins when:

You combine multiple tables
You need better performance

Always choose the method that keeps the query clear and efficient.

Common Mistakes

Forgetting parentheses
Returning multiple rows when using =
Writing complex nested queries without testing
Ignoring performance issues

First, test the inner query alone. Then build the outer query around it.

Final Thoughts

Subqueries and nested queries in SQL help you solve complex problems in a structured way. The inner query runs first. Then the outer query uses its result. You can use IN to compare values and EXISTS to check related data.

Start with simple examples. Then move to correlated queries. With practice, subqueries become easy to understand and use.