Write Single-Row and Multiple-Row Subqueries with SQL

Write Single-Row and Multiple-Row Subqueries

Single-Row Subqueries

Definition

A single-row subquery returns only one row and one column. It is used when you need to compare a single value in the outer query with a value derived from the subquery. This type of subquery is often used with operators that expect a single value, such as =, <, >, <=, >=, and <>.

Syntax: 

SELECT column_names
FROM table_name
WHERE column_name operator (subquery);

Example 1: Find Employees with the Highest Salary

Problem: Retrieve the details of the employee who has the highest salary.

Solution: 

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

Explanation:

• • The subquery (SELECT MAX(Salary) FROM Employees) calculates the maximum salary.
  • The outer query retrieves the details of the employee(s) whose salary matches this maximum value.

Example 2: Find Products Priced Above the Average Price

Problem: List products that are priced above the average price of all products.

Solution: 

SELECT ProductName, Price
FROM Products
WHERE Price > (
    SELECT AVG(Price)
    FROM Products
);

Explanation:

• • The subquery (SELECT AVG(Price) FROM Products) computes the average price of products.
  • The outer query selects products whose price is greater than this average price.

Example 3: Find Employees Working in the Same Department as Employee ‘John Doe’

Problem: Retrieve the names of employees working in the same department as ‘John Doe’.

Solution: 

SELECT EmployeeName
FROM Employees
WHERE DepartmentID = (
    SELECT DepartmentID
    FROM Employees
    WHERE EmployeeName = 'John Doe'
);

Explanation:

• • The subquery (SELECT DepartmentID FROM Employees WHERE EmployeeName = ‘John Doe’) retrieves the department ID for ‘John Doe’.
  • The outer query finds all employees in that department.

Multiple-Row Subqueries

Definition

A multiple-row subquery returns more than one row. It is used when you need to compare a value in the outer query with a set of values returned by the subquery. Multiple-row subqueries are typically used with operators that work with sets of values, such as IN, ANY, and ALL.

Syntax: 

SELECT column_names
FROM table_name
WHERE column_name operator (subquery);

Example 1: Find Employees in Specific Departments

Problem: Retrieve employees who work in departments with IDs 10, 20, or 30.

Solution: 

SELECT EmployeeName
FROM Employees
WHERE DepartmentID IN (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentID IN (10, 20, 30)
);

Explanation:

• • The subquery (SELECT DepartmentID FROM Departments WHERE DepartmentID IN (10, 20, 30)) returns a list of department IDs.
  • The outer query retrieves employees who work in any of these departments.

Example 2: Find Products with Prices Higher Than Any Product in Category ‘Electronics’

Problem: List products priced higher than any product in the ‘Electronics’ category.

Solution: 

SELECT ProductName, Price
FROM Products
WHERE Price > ANY (
    SELECT Price
    FROM Products
    WHERE Category = 'Electronics'
);

Explanation:

• • The subquery (SELECT Price FROM Products WHERE Category = ‘Electronics’) retrieves prices of products in the ‘Electronics’ category.
  • The outer query selects products whose price is greater than at least one price in this category.

Example 3: Find Employees with Salaries Greater Than the Average of Their Own Department

Problem: Retrieve employees whose salaries are greater than the average salary of their respective department.

Solution: 

SELECT EmployeeName, Salary
FROM Employees e1
WHERE Salary > ALL (
    SELECT AVG(Salary)
    FROM Employees e2
    WHERE e1.DepartmentID = e2.DepartmentID
    GROUP BY e2.DepartmentID
);

Explanation:

• • The subquery (SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID GROUP BY e2.DepartmentID) calculates the average salary for each department.
  • The outer query selects employees whose salary is greater than the average salary of their department.

Comparing Single-Row and Multiple-Row Subqueries

Single-Row Subqueries:

• • Return exactly one row and one column.
  • Use with operators like =, <, >, etc.
  • Ideal for comparisons where a single value is needed.

Multiple-Row Subqueries:

• • Return more than one row.
  • Use with operators like IN, ANY, ALL.
  • Ideal for comparisons where a set of values is needed.

Performance Considerations

• Indexing: Ensure columns used in subqueries are indexed to improve performance, especially with large datasets.
• Complexity: Complex subqueries can impact performance. Simplify subqueries when possible and use indexes to optimize query performance.
• Execution Plan: Use tools like EXPLAIN to understand the performance characteristics of your queries and optimize them as needed.