Learn Derived Tables with Nested Subqueries in FROM | Nested Subqueries in the FROM section

Swipe to show menu

Derived tables, also known as subqueries in the FROM clause, allow you to create a temporary result set that can be referenced as a table within your main query. This approach is especially useful when you need to break down complex logic into manageable steps or reuse aggregated data. By nesting a subquery in the FROM section, you can simplify intricate queries and make your SQL more modular.


              123456789101112131415161718
            
-- Calculate total sales per product, then filter for products with sales above $100
SELECT
    dt.product_id,
    p.product_name,
    dt.total_sales
FROM
    (
        SELECT
            product_id,
            SUM(total_amount) AS total_sales
        FROM
            sales
        GROUP BY
            product_id
    ) AS dt
    JOIN products p ON dt.product_id = p.product_id
WHERE
    dt.total_sales > 100;

Using derived tables in this way helps you organize your queries into logical building blocks. Instead of repeating calculations or making your main query overly complex, you can write a subquery that handles aggregation or filtering, then build on top of it. This leads to more readable, maintainable, and reusable SQL code, especially when working with multi-step data analysis.


              1234567891011121314151617181920
            
-- Join a derived table of total sales per product with product categories for enriched analysis
SELECT
    pc.category_name,
    p.product_name,
    dt.total_sales
FROM
    (
        SELECT
            product_id,
            SUM(total_amount) AS total_sales
        FROM
            sales
        GROUP BY
            product_id
    ) AS dt
    JOIN products p ON dt.product_id = p.product_id
    JOIN product_categories pc ON p.category_id = pc.category_id
ORDER BY
    pc.category_name,
    dt.total_sales DESC;

1. What is the main purpose of using a subquery in the FROM clause?

2. How can derived tables be used in SQL joins?

3. Fill in the blanks to select product names and their total sales using a subquery in the FROM clause:

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 2. Chapter 1