Sql Server Subqueries Complete Guide

What is a Subquery?

A subquery, also known as a nested query or inner query, is a query that is embedded within another query. The outer query, also called the main query, uses the results of the subquery to perform its operations. Subqueries can be used in various parts of a SQL statement, such as the SELECT, INSERT, UPDATE, and DELETE clauses, as well as in the WHERE clause and with HAVING.

Example 1: Subquery in the WHERE Clause

Objective: Find employees who earn more than the average salary.

Step-by-Step Explanation:

1. Calculate the Average Salary:

   SELECT AVG(Salary) FROM Employees;
   

2. Use the Subquery to find employees earning more than the average:

   SELECT EmployeeID, FirstName, LastName, Salary 
   FROM Employees 
   WHERE Salary > (SELECT AVG(Salary) FROM Employees);
   

Explanation:

• The subquery (SELECT AVG(Salary) FROM Employees) calculates the average salary of all employees.
• The outer query uses this result to filter out employees whose salaries are greater than the calculated average.

Example 2: Subquery with a Single Row

Objective: Find employees who work in the same department as John Doe.

Step-by-Step Explanation:

1. Find the DepartmentID of John Doe:

   SELECT DepartmentID 
   FROM Employees 
   WHERE FirstName='John' AND LastName='Doe';
   

2. Use the subquery to find all employees in that department:

   SELECT EmployeeID, FirstName, LastName, DepartmentID 
   FROM Employees 
   WHERE DepartmentID = (SELECT DepartmentID 
                         FROM Employees 
                         WHERE FirstName='John' AND LastName='Doe');
   

Explanatiion:

• The subquery finds the DepartmentID where John Doe works.
• The outer query retrieves all employees who work in the DepartmentID returned by the subquery.

Example 3: Using ALL with a Subquery

Objective: Find the departments whose maximum salary is higher than the highest salary in the Sales department.

Step-by-Step Explanation:

1. Find the highest salary in the Sales department:

   SELECT MAX(Salary) FROM Employees WHERE DepartmentName = 'Sales';
   

2. Use the subquery to list departments with salaries higher than the highest in Sales:

   SELECT DepartmentName 
   FROM Employees 
   GROUP BY DepartmentName 
   HAVING MAX(Salary) > ALL (SELECT MAX(Salary) 
                            FROM Employees 
                            WHERE DepartmentName = 'Sales');
   

Explanation:

• The subquery calculates the maximum salary in the Sales department.
• The ALL keyword in the HAVING clause is used by the outer query to ensure that only departments where the highest salary exceeds this value are selected.

Example 4: Subquery in the SELECT Clause

Objective: List each employee along with their department's average salary.

Step-by-Step Explanation:

1. Retrieve details of all employees along with their department IDs:

   SELECT EmployeeID, FirstName, LastName, DepartmentID FROM Employees;
   

2. Calculate the average salary for each department by using a subquery in the SELECT clause:

   SELECT EmployeeID, FirstName, LastName, DepartmentID,
          (SELECT AVG(Salary) 
           FROM Employees AS SubE 
           WHERE SubE.DepartmentID = E.DepartmentID) AS AvgDeptSalary
   FROM Employees AS E;
   

Explanation:

• The subquery (SELECT AVG(Salary) FROM Employees AS SubE WHERE SubE.DepartmentID = E.DepartmentID) calculates the average salary for the department where each employee works.
• This value is aliased as AvgDeptSalary and included in the result set of the outer query.

Example 5: Subquery in the INSERT Clause

Objective: Insert employees into a HighEarner table if their salary is higher than the minimum salary in another department, say IT.

Step-by-Step Explanation:

1. Find the Minimum Salary in the IT department:

   SELECT MIN(Salary) FROM Employees WHERE DepartmentName = 'IT';
   

2. Use the subquery to insert high earners into the HighEarner table:

   INSERT INTO HighEarner (EmployeeID, FirstName, LastName, Salary)
   SELECT EmployeeID, FirstName, LastName, Salary
   FROM Employees
   WHERE Salary > (SELECT MIN(Salary) FROM Employees WHERE DepartmentName = 'IT');
   

Explanation:

• The subquery determines the minimum salary in the IT department.
• The outer query selects all employees from the Employees table whose salaries are higher than this minimum value and inserts them into the HighEarner table.

Example 6: Subquery in the UPDATE Clause

Objective: Increase the salary of employees by 10% if their current salary is less than the average salary of their department.

Step-by-Step Explanation:

1. Calculate the average salary per department:

   SELECT AVG(Salary), DepartmentID 
   FROM Employees 
   GROUP BY DepartmentID;
   

2. Use the subquery to apply the condition in the UPDATE statement:

   UPDATE Employees 
   SET Salary = Salary * 1.10 
   WHERE Salary < (SELECT AVG(Salary) 
                   FROM Employees AS SubE 
                   WHERE SubE.DepartmentID = Employees.DepartmentID);
   

Explanation:

• The subquery (SELECT AVG(Salary) FROM Employees AS SubE WHERE SubE.DepartmentID = Employees.DepartmentID) calculates the average salary for each employee’s respective department.
• The outer query updates the Salary column for those employees whose salary is less than their department’s average salary.

Example 7: Subquery in the DELETE Clause

Objective: Delete employees who earn below the average salary across all departments.

Step-by-Step Explanation:

1. Calculate the average salary of all employees:

   SELECT AVG(Salary) FROM Employees;
   

2. Use the subquery in the DELETE statement to remove low earners:

   DELETE FROM Employees 
   WHERE Salary < (SELECT AVG(Salary) FROM Employees);
   

Explanation:

• The subquery calculates the overall average salary of all employees.
• The outer query deletes any records from the Employees table where the Salary is lower than this average.

Example 8: Correlated Subquery

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

Step-by-Step Explanation:

1. Identify the average salary for each department using a correlated subquery:

   SELECT EmployeeID, FirstName, LastName, Salary, DepartmentID 
   FROM Employees AS E 
   WHERE Salary > (SELECT AVG(Salary) 
                   FROM Employees AS SubE 
                   WHERE SubE.DepartmentID = E.DepartmentID);
   

Explanation:

• A correlated subquery references columns from the outer query (E.DepartmentID) in its WHERE clause.
• This means the subquery executes once for each row processed by the outer query.

This example achieves the same result as Example 3 but through a different approach.