[SQL] SQL Common Table Expressions (CTEs) for Beginners: Think in Steps, Not in Circles

 

This post breaks down the concept of Common Table Expressions (CTEs) using a simple "kitchen prep" analogy. It provides a side-by-side look at raw data, the CTE logic, and the final SQL query to help readers understand how to organize their data pipelines more effectively.

What is a Common Table Expression (CTE)?

A Common Table Expression (CTE) is a temporary, named result set in SQL that exists only for the duration of a single query. You can think of it as a "virtual table" or a "named subquery" that helps break down complex logic into smaller, more readable steps.

Key Characteristics

• Temporary Scope: It is created when the query starts and disappears as soon as the query finishes executing.
• Syntax: Defined using the WITH keyword at the very beginning of your SQL statement.
• Named & Reusable: Unlike standard subqueries, a CTE is assigned a specific name and can be referenced multiple times within the same main query.

Think of a Common Table Expression (CTE) as a "temporary result shelf"

Imagine you are cooking a complex meal. Instead of trying to chop veggies, sear meat, and boil pasta all in one giant pot at the exact same time, you chop the veggies first and put them in a temporary bowl on the side. When you're ready, you grab that bowl and add it to the main dish.

In SQL, a CTE is that temporary bowl. It holds a sub-result so you can reference it easily in your main query. 

The Syntax

You start a CTE with the word WITH.

WITH MyTemporaryBowl AS (

    -- Your "chopping" query goes here

    SELECT ...

)

-- Your "main cooking" query goes here

SELECT * FROM MyTemporaryBowl;

Real-World Example

Let’s say we have a table of Sales:

Goal: Find employees who sold more than the average total sales per person.

Step 1: Create the CTE (The "Bowl")

First, we calculate the total sales for everyone.

WITH TotalSalesCTE AS (

    SELECT Employee, SUM(SaleAmount) AS Total

    FROM Sales

    GROUP BY Employee

)

At this point, TotalSalesCTE looks like this:

Step 2: Use it in the Main Query

Now we use that "bowl" to filter the results.

WITH TotalSalesCTE AS (

    SELECT Employee, SUM(SaleAmount) AS Total

    FROM Sales

    GROUP BY Employee

)

SELECT Employee, Total

FROM TotalSalesCTE

WHERE Total > (SELECT AVG(Total) FROM TotalSalesCTE);

Final Result:

Conclusion

CTEs don't make your database faster, but they make you faster. They turn "spaghetti code" into a organized, step-by-step recipe that anyone on your team can follow.