SQL Subqueries Explained: Correlated, Nth-Highest Salary & More

What Is a Subquery in SQL?

A subquery is a query (SQL SELECT statement) inside another query. It is also called an inner query or nested query. Subqueries are used to retrieve data that depends on the results of another query from the same table or a different table.

A subquery can appear in the following clauses:

• SELECT
• FROM
• WHERE

Syntax

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

In this example:

• The subquery calculates the average salary.
• The outer query returns employees whose salary is greater than the average salary.

Correlated vs Non-Correlated Subqueries

Subqueries are broadly classified into two types:

• Non-correlated subquery
• Correlated subquery

Non-Correlated Subquery

A non-correlated subquery executes independently of the outer query. It does not reference any columns from the outer query and therefore runs only once.

Example

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

How It Works

1. The subquery calculates the average salary.
2. The subquery executes only once.
3. The outer query compares every employee's salary with that value.

Correlated Subquery

A correlated subquery depends on values from the outer query. Because it references columns from the outer query, it executes once for every row processed by the outer query.

Example

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

How It Works

For each employee:

1. The subquery calculates the average salary of that employee's department.
2. The employee's salary is compared with the department average.
3. Only employees earning above their department average are returned.

Correlated vs Non-Correlated Subquery Comparison

Finding the Second Highest Salary

There are multiple ways to find the second highest salary.

Method 1: Using ORDER BY with LIMIT and OFFSET (MySQL/PostgreSQL)

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Explanation

• ORDER BY salary DESC sorts salaries from highest to lowest.
• OFFSET 1 skips the highest salary.
• LIMIT 1 returns the next salary.
• DISTINCT removes duplicate salary values.

Method 2: Using MAX() with a Subquery

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

Explanation

1. The inner query finds the highest salary.
2. The outer query finds the maximum salary that is less than the highest salary.
3. That value is the second-highest salary.

Note: For the nth highest salary, the LIMIT and OFFSET approach can easily be extended by changing the offset value.

Employees Earning More Than the Average Salary

A common interview question is to find employees whose salary is greater than the average salary.

Example

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

How It Works

1. The subquery calculates the average salary.
2. Since it is a non-correlated subquery, it executes only once.
3. The outer query returns employees whose salary is greater than the calculated average.

Can You Use ORDER BY Inside a Subquery?

Yes, but it is useful only in certain situations.

When ORDER BY Is Useful

It is commonly used with clauses such as:

• LIMIT
• OFFSET
• TOP (SQL Server)

Example:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Here, ORDER BY determines which rows are selected before applying LIMIT.

When ORDER BY Has No Effect

If a subquery simply returns values for comparison (such as with IN, EXISTS, or comparison operators), adding an ORDER BY usually has no impact on the final result and may introduce unnecessary overhead.

FAQs

1. What Is a Subquery in SQL?

A subquery is a query nested inside another SQL query. It is also known as an inner query or nested query. Subqueries are used to retrieve data based on the results of another query and can appear in the SELECT, FROM, or WHERE clause.

2. What Is the Difference Between a Correlated and a Non-Correlated Subquery?

Non-Correlated Subquery

• Executes independently of the outer query.
• Runs only once.
• Generally offers better performance.

Correlated Subquery

• Depends on values from the outer query.
• Executes once for every row processed by the outer query.
• Typically slower than a non-correlated subquery.

3. Which Type of Subquery Is Generally Faster?

A non-correlated subquery is generally faster because it executes only once.

A correlated subquery is usually slower since it executes repeatedly for every row returned by the outer query.

4. How Do You Find the Second Highest Salary?

Using ORDER BY with LIMIT and OFFSET

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Using MAX() with a Subquery

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

5. How Do You Fetch Employees Earning More Than the Average Salary?

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

The subquery calculates the average salary, and the outer query returns employees earning more than that average.

6. Can You Use ORDER BY Inside a Subquery?

Yes. The ORDER BY clause can be used inside a subquery, but it is generally meaningful only when combined with clauses such as LIMIT, OFFSET, or TOP. Otherwise, it usually has no effect on the outer query's result and may introduce unnecessary overhead.