<aside> 🔍 A subquery is a query nested inside another query. It can appear in FROM, SELECT, WHERE, or JOIN clauses.

</aside>

Types of Subquery Results

• Scalar — Returns a single value (one row, one column)
• Row — Returns one row with multiple columns
• Table — Returns multiple rows and columns

1. Subquery in FROM Clause

Treat the subquery as a derived table.

-- Products priced above average
SELECT *
FROM (
    SELECT ProductID, Price, AVG(Price) OVER () AS AvgPrice
    FROM Sales.Products
) AS t
WHERE Price > AvgPrice;

-- Rank customers by total sales
SELECT *, RANK() OVER (ORDER BY TotalSales DESC) AS CustomerRank
FROM (
    SELECT CustomerID, SUM(Sales) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
) AS t;

2. Subquery in SELECT

-- Add total orders count to each product row
SELECT
    ProductID, Product, Price,
    (SELECT COUNT(*) FROM Sales.Orders) AS TotalOrders
FROM Sales.Products;

3. Subquery in JOIN

-- Customer details with total sales
SELECT c.*, t.TotalSales
FROM Sales.Customers AS c
LEFT JOIN (
    SELECT CustomerID, SUM(Sales) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
) AS t ON c.CustomerID = t.CustomerID;

4. Subquery with Comparison Operators

-- Products priced above average
SELECT ProductID, Price,
    (SELECT AVG(Price) FROM Sales.Products) AS AvgPrice
FROM Sales.Products
WHERE Price > (SELECT AVG(Price) FROM Sales.Products);