“Finding Above-Average Earners by Department with Nested Subqueries in SQL”

A nested subquery in SQL is a query within another query. The inner query (subquery) is executed first, and its result is used by the outer query.

Here’s a classic example using a nested subquery to find employees earning more than the average salary of their department.

Example

Schema

Consider the following two tables:

1. Employees

2. Department

Query

Find employees who earn more than the average salary of their department:

SELECT Name, Salary, DepartmentID
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees AS E
    WHERE E.DepartmentID = Employees.DepartmentID
);

Explanation

1. Inner Query: SELECT AVG(Salary) FROM Employees AS E WHERE E.DepartmentID = Employees.DepartmentID
   • Calculates the average salary for a specific department (filtered by DepartmentID).
2. Outer Query: SELECT Name, Salary, DepartmentID FROM Employees WHERE Salary > (...);
   • Retrieves employees whose salary is greater than the department’s average salary.

Output

Based on the data:

Name   | Salary | DepartmentID
--------------------------------
Alice  | 70000  | 1
Carol  | 80000  | 2

This is an example of how a nested subquery works in SQL. It allows dynamic, context-sensitive filtering based on the results of another query.

The average salary is calculated per department. This is because the inner query includes a condition:

WHERE E.DepartmentID = Employees.DepartmentID

This condition ensures that the average is specific to the department of the employee being evaluated in the outer query. In simpler terms:

• For each employee in the outer query, the inner query calculates the average salary only for that employee’s department.

Example Breakdown

Data Recap

Employees Table:

markdownCopy codeEmployeeID | Name   | DepartmentID | Salary
---------------------------------------------
1          | John   | 1            | 50000
2          | Alice  | 1            | 70000
3          | Bob    | 2            | 60000
4          | Carol  | 2            | 80000

Inner Query for John (DepartmentID = 1):

SELECT AVG(Salary)
FROM Employees AS E
WHERE E.DepartmentID = 1;

• This calculates the average salary for HR (DepartmentID = 1): Average=50000+700002=60000\text{Average} = \frac{50000 + 70000}{2} = 60000Average=250000+70000​=60000

Outer Query Check for John:

sqlCopy codeWHERE Salary > 60000;

• John’s salary (50000) is not greater than 60000, so John is excluded.

Inner Query for Carol (DepartmentID = 2):

SELECT AVG(Salary)
FROM Employees AS E
WHERE E.DepartmentID = 2;

• This calculates the average salary for IT (DepartmentID = 2):
• Average = (60000 + 80000)/2 = 70000

Outer Query Check for Carol:

WHERE Salary > 70000;

• Carol’s salary (80000) is greater than 70000, so Carol is included.

---

If you wanted the average salary across all departments combined, you would remove the WHERE clause in the inner query:

SELECT Name, Salary, DepartmentID
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
);

This calculates a single average salary for the entire company, comparing each employee’s salary against that overall average.