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.

Try It: Ties, Nth Highest and a Common Error

We ran these on MariaDB against six employees — including a tie: Asha and Ravi both earn 90,000.

CREATE TABLE employees (employee_id INT PRIMARY KEY, employee_name VARCHAR(20), department_id INT, salary INT);
INSERT INTO employees VALUES (1,'Asha',10,90000), (2,'Ravi',10,90000), (3,'Meera',20,75000),
                             (4,'Kiran',20,60000), (5,'Arjun',10,50000), (6,'Divya',30,75000);

The DISTINCT isn't optional — without it, ties break the answer. This is exactly the follow-up interviewers ask: "what if two people share the highest salary?"

Nth highest with a window function

SELECT employee_name, salary
FROM (
    SELECT employee_name, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 3;                      -- Kiran, 60000

DENSE_RANK() gives tied salaries the same rank without gaps (90000 → 1, 75000 → 2, 60000 → 3), so changing 3 to any N answers the "Nth highest salary" question — and it returns every employee at that salary, not just one. Window functions need MySQL 8+, MariaDB 10.2+, PostgreSQL or SQL Server.

The correlated subquery, run

The "above their department average" query returned Asha, Ravi (department 10, average 76,667) and Meera (department 20, average 67,500). Divya is alone in department 30, so her salary equals the average and she's correctly excluded — a good edge case to include in your own test data.

"Subquery returns more than 1 row"

SELECT employee_name FROM employees
WHERE salary = (SELECT salary FROM employees WHERE department_id = 10);
-- ERROR 1242 (21000): Subquery returns more than 1 row

A subquery used with =, > or < must return exactly one value. When it can return several, use IN, ANY/ALL, or EXISTS instead.

Practice these questions under interview conditions in the SQL Interview Simulator. More: Top 25 SQL Questions for Testers.

📚 Official documentation: MySQL Reference Manual

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. You may also find SQL Aggregate Functions & Grouping useful.

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.