Structuring Queries with CTEs

Welcome to Structuring Queries with Common Table Expressions. We are beginning Unit 1, the first of four units in this course, so this is our starting point for organizing complex queries with named stages. We will improve an order report by giving its intermediate order total calculation a clear name.

As we build on the SELECT, JOIN, GROUP BY, and subquery skills from the earlier SQL path, we will keep the calculation itself unchanged. We will move it into a named order_totals Common Table Expression with MySQL’s WITH clause, then use that result to add order dates and reliable chronological ordering. By the end, we will understand how this structural change improves readability and maintenance.

Before we refactor anything, let’s recognize the starting shape. A subquery in the FROM clause is a derived table: the outer query reads its rows as if they came from a table. The inner query below joins each line item to its product, then reduces many line items to one total for each order.

SELECT
    ot.order_id,
    ot.order_total
FROM (
    SELECT
        oi.order_id,
        ROUND(SUM(oi.quantity * p.product_price), 2) AS order_total
    FROM OrderItems AS oi
    INNER JOIN Products AS p
        ON p.product_id = oi.product_id
    GROUP BY oi.order_id
) AS ot
ORDER BY ot.order_id;

Here, oi.quantity * p.product_price calculates the value of one line item. SUM adds those values within each order_id, and ROUND(..., 2) keeps the result at two decimal places. The alias ot names the derived table so the outer query can select from it. The query is correct, but the calculation is tucked inside the main statement, which makes its purpose harder to spot and harder to reference more than once.

Now we can give that intermediate result a name before the main query begins. In MySQL 8.0, a WITH clause defines a Common Table Expression, or CTE. We move the derived table’s inner query into order_totals while preserving every part that determines the answer.

WITH order_totals AS (
    SELECT
        oi.order_id,
        ROUND(SUM(oi.quantity * p.product_price), 2) AS order_total
    FROM OrderItems AS oi
    INNER JOIN Products AS p
        ON p.product_id = oi.product_id
    GROUP BY oi.order_id
)
SELECT
    order_id,
    order_total
FROM order_totals
ORDER BY order_id;

The calculation stays unchanged: the same aliases connect OrderItems and Products, the same multiplication creates line-item value, GROUP BY creates one group for each order, SUM adds the values within each group, and ROUND creates the order_total column. This is a structural refactor, not a new calculation, so the totals remain equivalent to the derived table’s totals.

order_total is the gross order value calculated from the quantities and prices on an order. Because this report includes every recorded order, including canceled and refunded orders, the measure is order value rather than recognized revenue.

With the CTE defined, the main query can read from order_totals just as it would read from a table. At this stage, we request the two columns created by the CTE: the order identifier and its rounded total.

SELECT
    ot.order_id,
    ot.order_total
FROM order_totals AS ot
ORDER BY ot.order_id

The alias ot identifies the CTE in the main query, so ot.order_id and ot.order_total make each source clear. The aggregation no longer crowds the main SELECT; the main statement consumes a prepared result.

A CTE is not permanent, however. Its name exists only for the single statement immediately following the WITH clause.

Within that single statement, we may reference the CTE name as many times as needed. The query above uses order_totals once, but the same statement could join it and also reference it in a subquery.

Once the statement finishes, the name is gone. A later SELECT cannot use order_totals, because a CTE is not a view or a temporary table. So "reuse" means multiple references inside one statement, not availability across statements.

Now we can finish the report without changing order_totals. The CTE handles aggregation; the main query joins those totals to Orders, selects the fields needed in the report, and controls the presentation order.

The following flow shows how the CTE prepares one total per order before the main query builds the final report.

WITH order_totals AS (
    SELECT
        oi.order_id,
        ROUND(SUM(oi.quantity * p.product_price), 2) AS order_total
    FROM OrderItems AS oi
    INNER JOIN Products AS p
        ON p.product_id = oi.product_id
    GROUP BY oi.order_id
)
SELECT
    o.order_id,
    o.order_date,
    ot.order_total
FROM Orders AS o
INNER JOIN order_totals AS ot
    ON ot.order_id = o.order_id
ORDER BY o.order_date, o.order_id;

The aliases o and ot identify the two sources. The join matches their shared order_id, so each order receives its date and calculated total. ORDER BY o.order_date, o.order_id gives chronological order, then uses the unique order ID to break date ties. This total ordering makes the output deterministic. For the seeded data, the completed statement returns one row for each of the 600 orders.

We have transformed a nested derived table into a named CTE without changing the order total calculation. WITH order_totals AS (...) gives the intermediate result a meaningful name, while the following SELECT decides whether to inspect those totals directly or join them to order details. The CTE returns one row per order, and its scope lasts only for the statement that follows, where it may be referenced one or more times.

The complete report separates two jobs: the CTE calculates each order's gross order value with SUM(oi.quantity * p.product_price) and rounding, while the main query joins to Orders and applies deterministic chronological ordering. In the upcoming practices, we will inspect and run the derived-table baseline, refactor it into order_totals without changing its results, test statement-local scope, and extend the same statement into the full report so we can turn this pattern into confident SQL work.