Types of Problems Subqueries Can Solve with SQL

Types of Problems Subqueries Can Solve

Filtering Data Based on Complex Criteria

Subqueries are commonly used to filter data based on complex criteria that involve comparisons with aggregated values or results from other tables.

Example: Filtering by Aggregated Values

Problem: Find employees who earn more than the average salary of all employees.

Solution: 

SELECT Name, 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 main query then selects employees whose salary is higher than this average.

Performing Comparisons Across Tables

Subqueries can compare data between tables to filter or retrieve data based on relationships.

Example: Comparing Across Tables

Problem: Retrieve the names of employees who work 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) finds department IDs with budgets above $50,000. The main query then retrieves names of employees who work in these departments.

Calculating Values Based on Subset Data

Subqueries can be used to calculate values based on a subset of data, providing a basis for comparisons or filtering.

Example: Calculating Maximum Value

Problem: Find the employee with the highest salary in each department.

Solution: 

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

• Explanation: The subquery (SELECT MAX(Salary) FROM Employees e2 WHERE e.DepartmentID = e2.DepartmentID) calculates the maximum salary within each department. The main query retrieves employees whose salary matches this maximum value.

Checking Existence or Non-Existence of Related Data

Subqueries can be used to check if certain data exists or does not exist in a related table.

Example: Checking Existence

Problem: List departments that have at least one employee.

Solution: 

SELECT DepartmentID, DepartmentName
FROM Departments d
WHERE EXISTS (SELECT 1 FROM Employees e WHERE e.DepartmentID = d.DepartmentID);

Explanation: The subquery (SELECT 1 FROM Employees e WHERE e.DepartmentID = d.DepartmentID) checks if there is at least one employee in each department. The EXISTS operator returns true if the subquery returns any rows.

Example: Checking Non-Existence

Problem: Find departments that do not have any employees.

Solution: 

SELECT DepartmentID, DepartmentName
FROM Departments d
WHERE NOT EXISTS (SELECT 1 FROM Employees e WHERE e.DepartmentID = d.DepartmentID);

• Explanation: The subquery (SELECT 1 FROM Employees e WHERE e.DepartmentID = d.DepartmentID) checks if there are no employees in the department. The NOT EXISTS operator returns true if the subquery returns no rows.

Handling Hierarchical or Recursive Data

Subqueries can be used to handle hierarchical data or perform recursive operations.

Example: Finding Managerial Hierarchies

Problem: Retrieve employees who report directly to a specific manager.

Solution: 

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

• Explanation: The subquery (SELECT EmployeeID FROM Employees WHERE Name = ‘John Doe’) finds the ID of the manager named ‘John Doe’. The main query retrieves employees who report to this manager.

Aggregating Data Based on Subquery Results

Subqueries can be used within aggregation functions to compute values based on the results of another query.

Example: Aggregating with Subquery

Problem: Find departments where the average employee salary is greater than $60,000.

Solution: 

SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > (SELECT AVG(Salary) FROM Employees);

• Explanation: The subquery (SELECT AVG(Salary) FROM Employees) calculates the overall average salary. The HAVING clause then filters departments based on whether their average salary exceeds this value.

Using Subqueries as Derived Tables

Subqueries can be used in the FROM clause to create temporary result sets, which can then be queried further.

Example: Using Subquery as a Derived Table

Problem: Find employees whose salary is above the average salary for their department.

Solution: 

WITH DepartmentAverage AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT e.Name, e.Salary, da.AvgSalary
FROM Employees e
JOIN DepartmentAverage da ON e.DepartmentID = da.DepartmentID
WHERE e.Salary > da.AvgSalary;

• Explanation: The Common Table Expression (CTE) DepartmentAverage computes the average salary for each department. The main query then joins this CTE with the Employees table to find employees with salaries above their department’s average.

Summary

Subqueries are versatile tools in SQL that help solve a variety of problems, such as filtering data based on complex criteria, comparing data across tables, calculating values based on subsets, and handling hierarchical data. By understanding and using subqueries effectively, you can address complex data retrieval and manipulation tasks with greater precision.