4.6 Subqueries

Subqueries in SQL are queries embedded within another query. They can be used to retrieve data that will be used in the main query's condition. Subqueries can appear in various clauses such as SELECT, FROM, and WHERE. Let's explore some examples:

4.6.1 Basic Subquery

Consider the following scenario where you want to find employees in the "Employees" table who have salaries greater than the average salary:

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

EmployeeID	FirstName	LastName	Salary
2	Jane	Doe	60000
4	John	Smith	70000
5	Mary	Johnson	80000

In this example, the subquery (SELECT AVG(Salary) FROM Employees) calculates the average salary, and the main query retrieves employees with salaries greater than that average.

4.6.2 Correlated Subquery

A correlated subquery refers to a subquery that depends on the outer query. Let's find employees whose salaries are higher than the average salary in their department:

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

EmployeeID	FirstName	LastName	DepartmentID	Salary
4	John	Smith	1	70000
5	Mary	Johnson	1	80000
8	Michael	Brown	2	75000
9	Jessica	Davis	2	82000

Here, the subquery compares salaries within the same department by using the correlated condition WHERE DepartmentID = e.DepartmentID.

4.6.3 Subquery in FROM Clause

Subqueries can also appear in the FROM clause, creating a derived table. Let's find the total sales per employee:

SELECT e.EmployeeID, e.FirstName, e.LastName, TotalSales
FROM Employees e
JOIN (SELECT EmployeeID, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY EmployeeID) s
ON e.EmployeeID = s.EmployeeID;

EmployeeID	FirstName	LastName	TotalSales
1	John	Doe	95000
2	Jane	Doe	120000
3	Bob	Johnson	80000
4	John	Smith	110000
5	Mary	Johnson	90000
6	Emily	Davis	75000
7	James	Wilson	60000
8	Michael	Brown	100000
9	Jessica	Davis	85000

This example shows a subquery in the FROM clause, creating a derived table (aliased as s) to calculate the total sales per employee.

Feel free to execute these queries in your SQL environment to see the results. Subqueries are powerful tools for creating flexible and dynamic SQL queries.