Nested Queries (Subquery)

A nested query, also known as a subquery, is a query within another SQL query. The inner query is executed first, and its result is used by the outer query. Subqueries can be used to filter, calculate, or manipulate data, and they allow you to perform more complex queries in a modular fashion.

Types of Subqueries:

1. Single-row subquery: Returns a single value.
2. Multi-row subquery: Returns multiple rows.
3. Correlated subquery: Uses values from the outer query.

Syntax:

Simple Nested Query (Subquery):

SELECT column1
FROM table1
WHERE column2 = (SELECT column_name FROM table2 WHERE condition);

Correlated Nested Query:

SELECT column1
FROM table1 AS t1
WHERE EXISTS (SELECT column_name FROM table2 AS t2 WHERE t2.column2 = t1.column1);

1. Single-Row Subquery

This type of subquery returns only one row and is typically used with comparison operators like =, >, <, etc.

Example:

Suppose we have an Employees table:

We want to find the details of the employee with the highest salary. Here's the query:

SELECT FirstName, LastName
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);

• Inner Query: (SELECT MAX(Salary) FROM Employees) returns the highest salary.
• Outer Query: The outer query retrieves the employee(s) with that salary.

Result:

2. Multi-Row Subquery

A multi-row subquery returns multiple rows and is typically used with operators like IN, ANY, ALL.

Example:

You want to find employees who work in departments located in New York. Assume we have an Employees table and a Departments table:

Here’s the query:

SELECT FirstName
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');

• Inner Query: Retrieves the department IDs for departments located in New York.
• Outer Query: Finds the employees working in those departments.

Result:

3. Correlated Subquery

A correlated subquery refers to a column from the outer query and runs once for each row of the outer query. It depends on the outer query and cannot be run independently.

Example:

Find employees whose salary is higher than the average salary of their department. Assume we have the same Employees table with a DepartmentID column.

SELECT FirstName, Salary
FROM Employees E1
WHERE Salary > (SELECT AVG(Salary) 
               FROM Employees E2 
               WHERE E1.DepartmentID = E2.DepartmentID);

• Inner Query: (SELECT AVG(Salary) FROM Employees E2 WHERE E1.DepartmentID = E2.DepartmentID) calculates the average salary for each department.
• Outer Query: Compares the salary of each employee with the average salary of their respective department.

Result:

Subqueries in FROM Clause

You can also use a subquery in the FROM clause, treating the subquery as a temporary table.

Example:

SELECT AVG(Salary) AS AvgSalary
FROM (SELECT Salary FROM Employees WHERE DepartmentID = 101) AS SalesDept;

This query calculates the average salary of employees in the Sales department (DepartmentID = 101).