When writing SQL queries, sometimes a single query is not enough to solve a problem.

For example, you may want to:

• Find employees who earn more than the average salary.
• Find products that cost more than the average product price.
• Find customers who have placed at least one order.
• Find the second-highest salary.
• Find employees working in a particular department.
• Find products that have never been ordered.
• Compare a value against results returned by another query.

In situations like these, SQL provides a powerful feature called a Subquery.

A subquery allows you to place one SQL query inside another SQL query. The inner query produces a result that the outer query can use.

In this complete guide, you will learn what SQL subqueries are, how they work, their syntax, different types of subqueries, scalar and multi-row subqueries, correlated subqueries, IN, EXISTS, ANY, ALL, subqueries with SELECT, WHERE, FROM, and HAVING, practical examples, common mistakes, performance considerations, and when to use subqueries instead of JOIN.

---

What Is a Subquery in SQL?

A subquery is a SQL query written inside another SQL query.

It is also commonly called a:

• Nested query
• Inner query
• Inner SELECT

The query containing the subquery is called the outer query.

A simple example is:

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

Here, the inner query:

SELECT AVG(salary)
FROM employees

calculates the average salary.

The outer query then finds employees whose salary is greater than that average.

Simple Concept

Outer Query
    ↓
Uses result of
    ↓
Inner Query

This is the basic idea behind SQL subqueries.

---

Why Are SQL Subqueries Important?

Subqueries allow you to solve problems where the result of one query is required by another query.

For example, consider the question:

Which employees earn more than the average salary?

You cannot simply write:

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

This does not work because AVG() is an aggregate calculation and cannot be used directly in the WHERE clause like that.

Instead, use a subquery:

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

The subquery calculates the average, and the outer query uses that value.

---

Basic SQL Subquery Syntax

The general structure is:

SELECT column1, column2
FROM table_name
WHERE column_name operator (
    SELECT column_name
    FROM another_table
    WHERE condition
);

The inner query is enclosed in parentheses:

(
    SELECT ...
)

For example:

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

---

How SQL Subqueries Work

Consider this query:

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

Conceptually, SQL performs the following steps:

Step 1: Execute the inner query

SELECT AVG(salary)
FROM employees;

Suppose the result is:

60000

Step 2: Use the result in the outer query

The outer query effectively becomes:

SELECT name, salary
FROM employees
WHERE salary > 60000;

Step 3: Return matching employees

The final result contains employees earning more than the average salary.

The database optimizer may internally execute the query differently, but this is a useful way to understand the logical idea.

---

Types of SQL Subqueries

There are several ways to classify subqueries.

The most important types are:

1. Scalar Subquery
2. Single-Row Subquery
3. Multi-Row Subquery
4. Multi-Column Subquery
5. Correlated Subquery
6. Non-Correlated Subquery
7. Subquery in WHERE
8. Subquery in FROM
9. Subquery in SELECT
10. Subquery in HAVING

Let’s understand each one.

---

1. Scalar Subquery

A scalar subquery returns one value.

For example:

SELECT AVG(salary)
FROM employees;

The result might be:

65000

You can use this value in another query:

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

Because the subquery returns one value, it is called a scalar subquery.

Common examples

Scalar subqueries are useful with:

=
>
<
>=
<=
<>

For example:

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

---

2. Single-Row Subquery

A single-row subquery returns one row.

For example:

SELECT salary
FROM employees
WHERE employee_id = 10;

If employee ID 10 has a salary of 70,000, the query returns:

70000

You can use that result in another query:

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE employee_id = 10
);

This finds employees earning more than employee 10.

Important

The subquery must return at most one row when used with operators such as:

=
>
<
>=
<=
<>

If it returns multiple rows, many database systems will raise an error.

---

3. Multi-Row Subquery

A multi-row subquery returns multiple rows.

For example:

SELECT department_id
FROM departments
WHERE location = 'Jaipur';

This might return:

1
3
5

You cannot normally compare that result using a simple = operator.

Instead, use operators such as:

IN
ANY
ALL

For example:

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

The inner query returns multiple department IDs, and IN checks whether each employee belongs to one of those departments.

---

4. Multi-Column Subquery

A subquery can return multiple columns.

For example:

SELECT department_id, job_id
FROM employees
WHERE salary > 50000;

A multi-column subquery can be compared using row-value expressions where supported:

SELECT *
FROM employees
WHERE (department_id, job_id) IN (
    SELECT department_id, job_id
    FROM job_assignments
);

This compares combinations of values rather than a single column.

Support and syntax can vary between database systems, so check your database documentation for advanced row comparisons.

---

5. Correlated Subquery

A correlated subquery is a subquery that refers to a column from the outer query.

For example:

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

Here, the inner query references:

e1.department_id

from the outer query.

The subquery calculates the average salary for the employee’s department.

The outer query then checks whether that employee earns more than their department’s average.

Result

You get employees whose salary is higher than the average salary of their own department.

---

Correlated vs Non-Correlated Subqueries

Non-Correlated Subquery

The inner query does not depend on the outer query.

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

The average salary is calculated independently.

---

Correlated Subquery

The inner query depends on the outer query.

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

The average is calculated based on the current employee’s department.

---

Subquery with WHERE

The WHERE clause is one of the most common places to use subqueries.

For example:

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

This returns products whose price is greater than the average product price.

---

Subquery with IN

IN is useful when a subquery returns multiple values.

Example:

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

The inner query finds departments located in Jaipur.

The outer query finds employees belonging to those departments.

---

Subquery with NOT IN

NOT IN can be used to find values that do not appear in the subquery result.

For example:

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

This returns employees whose department is not among the departments located in Jaipur.

Important NULL Warning

NOT IN can produce unexpected results if the subquery can return NULL.

For example:

WHERE department_id NOT IN (
    SELECT department_id
    FROM departments
);

If the subquery contains NULL, SQL’s three-valued logic can cause comparisons to evaluate to UNKNOWN.

For many “find rows with no match” situations, NOT EXISTS is safer and clearer.

---

Subquery with EXISTS

EXISTS checks whether the subquery returns at least one row.

Example:

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

This returns customers who have at least one order.

The actual selected value inside EXISTS is not important. Therefore:

SELECT 1

is commonly used.

---

How EXISTS Works

For every customer, SQL checks whether a matching order exists.

Conceptually:

Customer
   ↓
Does a matching order exist?
   ↓
YES → Include customer
NO  → Exclude customer

EXISTS is particularly useful for relationship-based queries.

---

NOT EXISTS

NOT EXISTS does the opposite.

It checks whether no matching row exists.

For example:

SELECT name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

This finds customers who have never placed an order.

---

EXISTS vs IN

Both can sometimes solve similar problems.

Using IN:

SELECT name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

Using EXISTS:

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Both can identify customers with orders.

However, they are not always interchangeable, especially with NULL values and correlated conditions.

The database optimizer may transform either form into a similar execution strategy.

---

Subquery with ANY

ANY compares a value with values returned by a subquery.

For example:

SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id = 2
);

This means the employee’s salary must be greater than at least one salary returned by the subquery.

In other words:

salary > ANY(values)

is true when the salary is greater than at least one value.

---

Subquery with ALL

ALL requires the comparison to be true for every value returned by the subquery.

Example:

SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 2
);

This finds employees whose salary is greater than every salary returned by the subquery.

In practical terms, it can be used to find employees earning more than everyone in a particular group.

---

ANY vs ALL

Consider the values:

30000
40000
50000

For:

salary > ANY (...)

the salary only needs to be greater than at least one value.

For:

salary > ALL (...)

the salary must be greater than all three values.

Therefore:

ANY → At least one
ALL → Every value

---

Subquery with SELECT

A subquery can appear in the SELECT list.

For example:

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

The average salary is returned alongside every employee.

Example:

name	salary	average_salary
Rahul	70000	60000
Priya	50000	60000
Amit	60000	60000

This can be useful for displaying a comparison value.

However, if the same calculation can be expressed more clearly using a window function, window functions are often a better choice.

---

Subquery with FROM

A subquery can also appear in the FROM clause.

This is often called a derived table.

Example:

SELECT department_id, average_salary
FROM (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS department_summary;

The inner query creates a temporary result set.

The outer query reads from that result.

Conceptually

employees
   ↓
GROUP BY department_id
   ↓
department_summary
   ↓
Outer SELECT

A subquery in FROM normally needs an alias in databases that require derived tables to be named.

---

Subquery with HAVING

Subqueries can also be used in HAVING.

For example:

SELECT
    department_id,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM employees
);

This returns departments whose average salary is higher than the overall company average.

---

Subquery with UPDATE

Subqueries can also be used in data modification statements.

For example:

UPDATE employees
SET salary = salary * 1.10
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name = 'IT'
);

This increases salaries by 10% for employees belonging to the IT department.

Always test the SELECT version of a condition before running a production UPDATE.

---

Subquery with DELETE

A subquery can also be used with DELETE.

For example:

DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
);

This attempts to delete customers who have never placed an order.

However, because NOT IN has important NULL behavior, NOT EXISTS is often preferable:

DELETE FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Be extremely careful with DELETE operations and test the selection logic first.

---

Finding Employees Above Average Salary

This is one of the most common subquery examples.

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

What happens?

The subquery calculates:

Average salary

The outer query then selects employees earning more than that amount.

---

Finding the Highest Salary

You can use a subquery with MAX():

SELECT name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

This is better than using:

ORDER BY salary DESC
LIMIT 1

when you want all employees tied for the highest salary.

---

Finding the Second-Highest Salary

A classic SQL problem is finding the second-highest salary.

One approach is:

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

The inner query finds the highest salary.

The outer query excludes that salary and finds the maximum remaining salary.

Important

If you need the second distinct highest salary, this approach is useful.

If you need the second row after sorting, even when salaries tie, the definition is different and a window function such as ROW_NUMBER() may be more appropriate.

---

Finding Employees in the IT Department

You can use a subquery to find the department ID first:

SELECT name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'IT'
);

This assumes the department name uniquely identifies one department.

If multiple rows can match, use IN instead:

SELECT name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name = 'IT'
);

---

Finding Customers Who Placed Orders

Using EXISTS:

SELECT
    c.customer_id,
    c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

This is a very common pattern in customer management and e-commerce systems.

---

Finding Customers Without Orders

Using NOT EXISTS:

SELECT
    c.customer_id,
    c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

This finds customers with no matching orders.

---

Finding Products Above Average Price

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

This is useful for product analytics.

---

Finding Products More Expensive Than Every Product in a Category

SELECT
    product_name,
    price
FROM products
WHERE price > ALL (
    SELECT price
    FROM products
    WHERE category_id = 5
);

This returns products whose price is greater than every price returned for category 5.

---

Nested Subqueries

A subquery can contain another subquery.

For example:

SELECT name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE manager_id IN (
        SELECT employee_id
        FROM employees
        WHERE name = 'Rahul'
    )
);

Here:

Outer Query
   ↓
Department Subquery
   ↓
Manager Subquery

Nested subqueries can be powerful, but deeply nested SQL can become difficult to read and maintain.

If a query becomes too complicated, consider using:

• JOIN
• Common Table Expressions (WITH)
• Window functions
• Views

---

Multiple Subqueries in One Query

You can use more than one subquery.

For example:

SELECT
    name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)
AND department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Jaipur'
);

This finds employees who:

1. Earn more than the average salary.
2. Work in departments located in Jaipur.

---

Correlated Subquery Example: Above Department Average

Suppose you want employees earning more than the average salary of their own department.

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

The inner query changes based on the current employee’s department.

This is a classic correlated subquery.

---

Correlated Subquery Example: Most Recent Order

Suppose you want each customer’s latest order.

One possible approach is:

SELECT
    o.customer_id,
    o.order_id,
    o.order_date
FROM orders o
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

This returns orders whose date matches the latest order date for that customer.

Important

If a customer has multiple orders on the same latest date, this query can return multiple rows.

If you need exactly one row per customer, a window function such as ROW_NUMBER() with a deterministic tie-breaker is often a better solution.

---

Subqueries vs JOIN

Many SQL problems can be solved using either a subquery or a JOIN.

For example, using a subquery:

SELECT c.name
FROM customers c
WHERE c.customer_id IN (
    SELECT o.customer_id
    FROM orders o
);

A similar query can use JOIN:

SELECT DISTINCT c.name
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;

Both can identify customers with orders.

However, the best option depends on what you are trying to express.

---

When Should You Use a Subquery?

Subqueries are particularly useful when:

• You need a calculated value from another query.
• You need to compare against an aggregate.
• You need to check whether related rows exist.
• The inner query naturally represents a separate logical step.
• You need IN, EXISTS, ANY, or ALL.

Example:

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

This is easy to understand because the inner query directly represents the comparison value.

---

When Should You Use JOIN?

JOIN is often preferable when you need to retrieve columns from related tables.

For example:

SELECT
    c.name,
    o.order_id,
    o.amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;

A JOIN is often clearer when you are combining data from multiple tables.

---

Subquery vs JOIN: Simple Rule

A useful guideline is:

Use a subquery when one query’s result naturally acts as a condition or intermediate result for another query. Use a JOIN when you need to combine columns from related tables.

This is not an absolute rule. Modern database optimizers can transform many equivalent queries into similar execution plans.

---

Subqueries vs CTEs

A complex subquery can sometimes be rewritten using a Common Table Expression (CTE).

Subquery

SELECT *
FROM (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS summary
WHERE average_salary > 60000;

CTE

WITH summary AS (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM summary
WHERE average_salary > 60000;

The CTE version can be easier to read, especially when the query contains several logical steps.

---

Subqueries vs Window Functions

Some tasks traditionally solved with subqueries can be expressed more clearly using window functions.

For example, to compare an employee’s salary with the average salary:

Subquery approach

SELECT
    name,
    salary,
    salary - (
        SELECT AVG(salary)
        FROM employees
    ) AS difference
FROM employees;

A window-function approach can calculate the average across the result set without a separate scalar subquery:

SELECT
    name,
    salary,
    salary - AVG(salary) OVER () AS difference
FROM employees;

Window functions become especially useful for:

• Rankings
• Running totals
• Percentages
• Group comparisons
• Top-N-per-group problems

---

SQL Subquery Performance

Subqueries are not automatically slow.

Modern database optimizers can optimize many subqueries effectively.

However, performance can depend heavily on:

• Database engine
• Table size
• Indexes
• Query structure
• Correlation
• Data distribution
• Execution plan

---

Performance Tip 1: Index Correlated Columns

Consider:

SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

An index on:

orders.customer_id

may help the database efficiently locate matching orders.

---

Performance Tip 2: Avoid Unnecessary Correlation

A correlated subquery may logically be evaluated with reference to each outer row.

For example:

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

For large datasets, compare this with alternatives such as:

• Pre-aggregating by department
• Joining to the aggregate result
• Using a window function

The optimizer may already transform the query, so use an execution plan rather than assuming one form is always faster.

---

Performance Tip 3: Use EXPLAIN

If a subquery is slow, inspect the execution plan.

For databases that support it:

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

The execution plan can show whether the database is using:

• Indexes
• Table scans
• Joins
• Sorting
• Aggregation
• Semi-joins
• Other execution strategies

---

Common SQL Subquery Mistakes

Mistake 1: Forgetting Parentheses

Incorrect:

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

Correct:

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

---

Mistake 2: Using = with Multiple Rows

Potentially incorrect:

SELECT *
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE location = 'Jaipur'
);

If multiple departments are located in Jaipur, the subquery returns multiple rows.

Use:

SELECT *
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Jaipur'
);

---

Mistake 3: Confusing IN and EXISTS

IN compares a value against a set of values:

WHERE customer_id IN (...)

EXISTS checks whether a matching row exists:

WHERE EXISTS (...)

Choose based on the meaning of the query.

---

Mistake 4: Ignoring NULL with NOT IN

Consider:

WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

If the subquery can return NULL, the result may not behave as expected.

A safer pattern for “no matching row” is often:

WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

---

Mistake 5: Making Subqueries Too Deep

A query such as:

Query
 └── Subquery
      └── Subquery
           └── Subquery
                └── Subquery

can become difficult to understand and maintain.

Consider using a CTE:

WITH ...

or breaking the problem into clearer steps.

---

Mistake 6: Forgetting the Alias for a Derived Table

For example:

SELECT *
FROM (
    SELECT department_id, AVG(salary)
    FROM employees
    GROUP BY department_id
);

Some database systems require an alias for the derived table.

Use:

SELECT *
FROM (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS summary;

---

SQL Subquery Cheat Sheet

Requirement	Example
Single value	WHERE salary > (SELECT AVG(salary) ...)
Multiple values	WHERE id IN (SELECT id ...)
Check existence	WHERE EXISTS (SELECT 1 ...)
Check non-existence	WHERE NOT EXISTS (SELECT 1 ...)
At least one comparison	> ANY (...)
Every value comparison	> ALL (...)
Subquery in SELECT	(SELECT AVG(salary) ...)
Subquery in FROM	FROM (SELECT ...) AS x
Subquery in HAVING	HAVING AVG(...) > (SELECT ...)
Correlated subquery	Inner query references outer query
Nested subquery	Subquery inside another subquery

---

Real-World Uses of SQL Subqueries

Subqueries are widely used in real applications.

E-Commerce

They can help find:

• Products above average price
• Customers who placed orders
• Customers without orders
• Highest-value customers
• Products never purchased
• Orders above average order value

Example:

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

---

CRM

Subqueries can help identify:

• Leads assigned to specific teams
• Customers with open deals
• Salespeople with above-average revenue
• Customers with no recent activity

Example:

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM deals d
    WHERE d.customer_id = c.customer_id
);

---

ERP

Subqueries can be used for:

• Department comparisons
• Inventory analysis
• Supplier analysis
• Employee salary reports
• Sales comparisons

Example:

SELECT product_id, quantity
FROM inventory
WHERE quantity < (
    SELECT AVG(quantity)
    FROM inventory
);

---

Banking and Finance

Subqueries can support:

• Transaction comparisons
• Account analysis
• Customer activity
• Risk reports
• Balance comparisons

For example:

SELECT account_id, balance
FROM accounts
WHERE balance > (
    SELECT AVG(balance)
    FROM accounts
);

---

Best Practices for SQL Subqueries

Follow these practices when writing subqueries.

1. Keep the Query Readable

Use indentation:

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

Avoid writing everything on one line.

---

2. Use Meaningful Aliases

For correlated queries:

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

Aliases such as e and e2 make relationships clearer.

---

3. Choose the Correct Operator

Use:

=       → One value
IN      → Multiple possible values
EXISTS  → Check whether a row exists
ANY     → Compare against at least one value
ALL     → Compare against every value

---

4. Think About NULL Values

Pay special attention to:

NOT IN

when the subquery can return NULL.

NOT EXISTS is often a safer alternative for anti-match queries.

---

5. Test the Inner Query First

Before writing the complete query, run the subquery separately.

For example:

SELECT AVG(salary)
FROM employees;

Then use it in:

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

This makes debugging much easier.

---

6. Check the Execution Plan for Slow Queries

Use:

EXPLAIN

or your database’s equivalent when performance matters.

---

Frequently Asked Questions

What is a subquery in SQL?

A subquery is a SQL query placed inside another SQL query. The inner query produces a result that the outer query can use.

---

What is another name for a subquery?

A subquery is also commonly called a nested query or inner query.

---

Where can subqueries be used?

Depending on the database and query structure, subqueries can appear in:

• WHERE
• SELECT
• FROM
• HAVING
• INSERT
• UPDATE
• DELETE

---

What is a correlated subquery?

A correlated subquery is an inner query that references a value from the outer query.

Example:

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

---

What is the difference between IN and EXISTS?

IN compares a value against a set of values.

EXISTS checks whether the subquery produces at least one matching row.

---

Which is faster: JOIN or subquery?

There is no universal answer.

Modern database optimizers can transform many equivalent queries into similar execution plans. Performance depends on the database, indexes, data volume, query structure, and execution plan.

Use the form that clearly expresses the logic, then measure important queries using the database’s execution-plan tools.

---

Can a subquery return multiple rows?

Yes, but the outer query must use an operator that supports multiple rows, such as:

IN
ANY
ALL
EXISTS

A scalar comparison such as:

=

generally expects a single value.

---

Can a subquery contain another subquery?

Yes.

This is called a nested subquery.

However, deeply nested queries can become difficult to maintain, so consider CTEs, joins, or window functions when they improve clarity.

---

Is a subquery always slower than a JOIN?

No.

A subquery is not automatically slower than a JOIN. The optimizer may transform the query, and actual performance depends on the specific database and data.

---

Conclusion

SQL subqueries are one of the most powerful features for solving complex database problems.

A subquery allows one SQL query to provide a value, list, or existence check that another query can use.

The most important concepts to remember are:

• A subquery is a query inside another query.
• The inner query is also called a nested query.
• Scalar subqueries return a single value.
• Multi-row subqueries return multiple values.
• IN is useful when comparing against multiple values.
• EXISTS checks whether matching rows exist.
• NOT EXISTS checks for the absence of matching rows.
• ANY means at least one matching value satisfies the comparison.
• ALL means every returned value satisfies the comparison.
• Correlated subqueries reference columns from the outer query.
• Subqueries can appear in WHERE, SELECT, FROM, and HAVING, as well as data-modification statements.
• NOT EXISTS is often safer than NOT IN when NULL values are possible.
• Subqueries can sometimes be replaced by JOIN, CTEs, or window functions.
• Performance should be evaluated using indexes and execution plans rather than assumptions.

A useful SQL learning path is:

SELECT → WHERE → JOIN → GROUP BY → HAVING → ORDER BY → Subqueries → CTEs → Window Functions

Once you understand subqueries, you can solve much more advanced SQL problems involving data comparison, reporting, analytics, customer analysis, e-commerce data, employee reports, financial calculations, and complex filtering.