Nested subqueries in SQL

Introduction

Nested subqueries let you use one query inside another to find data step-by-step, like asking a question within a question.

When you want to filter results based on a condition that depends on another query.

When you need to find records related to the results of a different query.

When you want to compare values against a set of results from another query.

When you want to calculate something first and then use that result to get final data.

Syntax

SQL

SELECT column1, column2
FROM table1
WHERE column3 IN (
    SELECT column3
    FROM table2
    WHERE condition
);

The inner query (subquery) runs first and its result is used by the outer query.

Subqueries can be placed in WHERE, FROM, or SELECT clauses.

Examples

Find employees who work in departments located in New York.

SQL

SELECT name
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE location = 'New York'
);

Find products priced higher than the average price of all products.

SQL

SELECT product_name
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Find the customer who made order number 101.

SQL

SELECT customer_name
FROM customers
WHERE id = (
    SELECT customer_id
    FROM orders
    WHERE order_id = 101
);

Sample Program

This query finds employees whose salary is higher than the average salary of all employees.

SQL

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

Important Notes

Make sure the subquery returns a single column when used with IN or = operators.

If the subquery returns multiple rows but you use =, it will cause an error; use IN instead.

Nested subqueries can sometimes be replaced with JOINs for better performance.

Summary

Nested subqueries let you use one query inside another to filter or compare data.

They run the inner query first, then use its result in the outer query.

Use them to answer complex questions step-by-step in your database.