SQL
Structure Complex Queries with Derived Tables
Learn to use subqueries in the FROM clause (derived tables) to perform intermediate calculations, filter data, or simplify complex joins before final aggregation.
SELECT
a.category_name,
SUM(a.total_sales) AS total_sales_last_year
FROM (
SELECT
c.category_name,
SUM(oi.quantity * oi.price) AS total_sales
FROM Categories c
JOIN Products p ON c.category_id = p.category_id
JOIN OrderItems oi ON p.product_id = oi.product_id
JOIN Orders o ON oi.order_id = o.order_id
WHERE o.order_date >= '2023-01-01' AND o.order_date < '2024-01-01'
GROUP BY c.category_name
) AS a
GROUP BY a.category_name
ORDER BY total_sales_last_year DESC;
How it works: This query uses a derived table (the subquery aliased as 'a') to first calculate the total sales for each product category within a specific year. The outer query then takes these pre-aggregated results and performs a final aggregation or simply selects them, demonstrating how derived tables can break down complex logic into manageable steps, acting as a temporary result set. This is an alternative to CTEs for similar purposes.