Types of Subqueries with SQL

Types of Subqueries

Subqueries, or nested queries, are queries embedded within another query. They provide intermediate results that the main query can use. Here’s a detailed description of the different types of subqueries:

Single-Row Subqueries

Single-row subqueries return a single row and a single column. They are used in situations where you need to compare a single value to the result of the subquery.

Examples and Usage:

Example 1: Find the Employee with the Highest Salary

Problem: Retrieve the name and salary of the employee with the highest salary.

Solution: 

SELECT Name, Salary
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);

Explanation: The subquery (SELECT MAX(Salary) FROM Employees) returns a single value, which is the highest salary. The main query retrieves the employee with this salary.

Example 2: Find Employees Earning More Than a Specific Employee

Problem: List employees whose salaries are higher than that of ‘John Doe’.

Solution: 

SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT Salary FROM Employees WHERE Name = 'John Doe');

Explanation: The subquery (SELECT Salary FROM Employees WHERE Name = ‘John Doe’) returns the salary of ‘John Doe’. The main query selects employees with salaries greater than this value.

Multiple-Row Subqueries

Multiple-row subqueries return multiple rows and a single column. They are used with operators that can handle multiple values, such as IN, ANY, or ALL.

Examples and Usage:

Example 1: Find Employees in Specific Departments

Problem: Retrieve the names of employees working in departments with a budget greater than $50,000.

Solution: 

SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Budget > 50000);

Explanation: The subquery (SELECT DepartmentID FROM Departments WHERE Budget > 50000) returns multiple department IDs. The main query retrieves employees working in these departments.

Example 2: Find Employees with Salaries Greater Than the Average Salary in Their Department

Problem: List employees whose salaries are higher than the average salary in their respective departments.

Solution: 

SELECT Name, Salary
FROM Employees e
WHERE Salary > ALL (SELECT AVG(Salary)
                     FROM Employees
                     WHERE DepartmentID = e.DepartmentID);

Explanation: The subquery calculates the average salary for each department and returns multiple values. The main query selects employees whose salaries exceed these averages.

Correlated Subqueries

Correlated subqueries depend on the outer query. They reference columns from the outer query and execute once for each row processed by the outer query.

Examples and Usage:

Example 1: Find Employees with Salaries Greater Than the Average in Their Department

Problem: List employees who earn more than the average salary in their department.

Solution: 

SELECT Name, Salary, DepartmentID
FROM Employees e
WHERE Salary > (SELECT AVG(Salary)
                 FROM Employees e2
                 WHERE e.DepartmentID = e2.DepartmentID);

Explanation: The subquery calculates the average salary for the department of each employee in the outer query.

Example 2: Find Employees Reporting to a Specific Manager

Problem: Retrieve all employees who report to a manager with a specific ID.

Solution: 

SELECT Name
FROM Employees e
WHERE ManagerID = (SELECT ManagerID
                   FROM Employees
                   WHERE Name = 'John Doe');

Explanation: The subquery finds the manager ID of ‘John Doe’. The main query retrieves all employees reporting to this manager.

Nested Subqueries

Nested subqueries are subqueries within other subqueries. They are used for more complex operations where one subquery contains another subquery.

Examples and Usage:

Example 1: Find Employees with Salaries Higher Than the Average in High-Budget Departments

Problem: Retrieve employees whose salaries are higher than the average salary in departments with a budget greater than $50,000.

Solution: 

SELECT Name, Salary
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID IN (
        SELECT DepartmentID
        FROM Departments
        WHERE Budget > 50000
    )
);

Explanation: The nested subquery first finds departments with high budgets, then calculates the average salary in these departments. The main query retrieves employees earning more than this average.

Example 2: Find Departments with More Employees Than the ‘IT’ Department

Problem: List departments with more employees than the ‘IT’ department.

Solution: 

SELECT DepartmentID, DepartmentName
FROM Departments
WHERE (SELECT COUNT(*)
       FROM Employees
       WHERE DepartmentID = Departments.DepartmentID) > (
           SELECT COUNT(*)
           FROM Employees
           WHERE DepartmentID = (SELECT DepartmentID
                                   FROM Departments
                                   WHERE DepartmentName = 'IT')
);

Explanation: The innermost subquery retrieves the department ID for ‘IT’. The nested subquery counts employees in each department and compares it to the count of employees in the ‘IT’ department. The main query lists departments with more employees.

Scalar Subqueries

Scalar subqueries return a single value and are often used in the SELECT list or as part of expressions in the WHERE clause.

Examples and Usage:

Example 1: Display Employees with the Average Salary of Their Department

Problem: List employees along with the average salary of their department.

Solution: 

SELECT Name, Salary,
       (SELECT AVG(Salary)
        FROM Employees e2
        WHERE e2.DepartmentID = e.DepartmentID) AS DepartmentAverage
FROM Employees e;

 Explanation: The scalar subquery calculates the average salary for each employee’s department and is included in the SELECT list to show this average alongside each employee’s salary.

Example 2: Display the Difference Between Employee Salary and Department Average

Problem: Show employees and the difference between their salary and the average salary of their department.

Solution: 

SELECT Name, Salary,
       Salary - (SELECT AVG(Salary)
                  FROM Employees e2
                  WHERE e2.DepartmentID = e.DepartmentID) AS SalaryDifference
FROM Employees e;

Explanation: The scalar subquery calculates the average salary for the department of each employee, and the result is used to compute the difference with the employee’s salary.

Summary

Understanding the different types of subqueries and how to use them effectively can help you solve complex SQL queries:

• Single-Row Subqueries: For simple comparisons with single values.
• Multiple-Row Subqueries: For operations involving multiple values.
• Correlated Subqueries: For operations that depend on values from the outer query.
• Nested Subqueries: For complex queries with multiple layers of subqueries.
• Scalar Subqueries: For single value calculations used in expressions or selections.