A subquery is a query nested within another query. Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements or within clauses like WHERE, FROM, or HAVING. They help simplify complex SQL queries by breaking them into smaller parts.

Syntax

SELECT column_names
FROM table_name
WHERE column_name = (SELECT column_names FROM another_table WHERE condition);

Types of Subqueries

1. Single-row Subquery: Returns a single row.
2. Multi-row Subquery: Returns multiple rows.
3. Correlated Subquery: Refers to columns in the outer query.
4. Nested Subquery: A subquery within another subquery.

Example 1: Single-row Subquery

Goal: Find the customer who placed the earliest order.

Query

SELECT CustomerID, OrderDate
FROM Orders
WHERE OrderDate = (
    SELECT MIN(OrderDate)
    FROM Orders
);

Explanation

• The subquery finds the earliest OrderDate from the Orders table.
• The outer query retrieves the CustomerID and OrderDate for that earliest order.

Example 2: Multi-row Subquery

Goal: List all products that have been ordered.

Query

SELECT ProductName
FROM Products
WHERE ProductID IN (
    SELECT ProductID
    FROM OrderDetails
);

Explanation

• The subquery selects all ProductIDs from OrderDetails (products that have been ordered).
• The outer query retrieves the ProductName for these ProductIDs from the Products table.

Example 3: Correlated Subquery

Goal: Find employees who have placed orders.

Query

SELECT EmployeeID, LastName
FROM Employees e
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.EmployeeID = e.EmployeeID
);

Explanation

• The subquery checks if there are orders in the Orders table for each EmployeeID from the Employees table.
• The outer query retrieves employees who match the condition.

Example 4: Subquery in the FROM Clause

Goal: Find the total number of orders placed by each customer.

Query

SELECT CustomerID, TotalOrders
FROM (
    SELECT CustomerID, COUNT(OrderID) AS TotalOrders
    FROM Orders
    GROUP BY CustomerID
) AS CustomerOrders
WHERE TotalOrders > 5;

Explanation

• The inner query calculates the total number of orders (TotalOrders) for each customer.
• The outer query filters customers who have placed more than 5 orders.

Example 5: Subquery in the SELECT Clause

Goal: Retrieve all orders and include the total amount for each order.

Query

SELECT OrderID, 
       (SELECT SUM(Quantity * UnitPrice)
        FROM OrderDetails
        WHERE OrderDetails.OrderID = Orders.OrderID) AS TotalAmount
FROM Orders;

Explanation

• The subquery calculates the total amount for each order by summing Quantity * UnitPrice in the OrderDetails table.
• The outer query displays the OrderID and its corresponding TotalAmount.

Example 6: Subquery in the HAVING Clause

Goal: List categories with total sales greater than the average category sales.

Query

SELECT c.CategoryID, c.CategoryName, SUM(od.Quantity * od.UnitPrice) AS TotalSales
FROM Categories c
JOIN Products p ON c.CategoryID = p.CategoryID
JOIN OrderDetails od ON p.ProductID = od.ProductID
GROUP BY c.CategoryID, c.CategoryName
HAVING TotalSales > (
    SELECT AVG(TotalSales)
    FROM (
        SELECT SUM(od.Quantity * od.UnitPrice) AS TotalSales
        FROM Categories c
        JOIN Products p ON c.CategoryID = p.CategoryID
        JOIN OrderDetails od ON p.ProductID = od.ProductID
        GROUP BY c.CategoryID
    ) AS CategorySales
);

Explanation

• The inner subquery calculates the average sales per category.
• The outer query lists categories whose total sales exceed the average.

When to Use Subqueries

• Simplify complex logic: Break down tasks into smaller, more manageable queries.
• Filter data dynamically: Use correlated subqueries for dynamic filtering.
• Aggregate results: Nest subqueries to compute aggregate data.

Key Points

1. Performance: Subqueries can be less efficient than joins, especially for large datasets. Optimize queries or replace subqueries with joins if possible.
2. Correlated Subqueries: These are slower since they execute once for each row in the outer query.
3. Nested Subqueries: Can become hard to read. Use clear alias names for readability.