Beginner

612. JOIN with CTE

Use a Common Table Expression to create a temporary named result that can be JOINed.

📘 Lesson 612 of 728 ⏱ 14 min 🎯 Advanced

A Common Table Expression, or CTE, is a named temporary result defined using WITH. The CTE can then be referenced in the main query like a table.

CTEs improve readability and make complex SQL logic easier to organize into logical steps.

Calculate customer sales in a CTE and JOIN the result to customer details.

SQL 
WITH CustomerSales AS (
    SELECT
        CustomerID,
        SUM(OrderAmount) AS TotalSales
    FROM Orders
    GROUP BY CustomerID
)
SELECT
    c.CustomerName,
    cs.TotalSales
FROM Customers c
INNER JOIN CustomerSales cs
    ON c.CustomerID = cs.CustomerID;

Used in reporting, analytics, data preparation, recursive queries, and complex transformations.

  • CTEs are defined using WITH.
  • A CTE can be JOINed like a table.
  • CTEs improve query readability.
  • Aggregation can be performed inside the CTE.
  • CTEs exist for the duration of the statement.
What is a CTE?

A CTE is a named temporary result set defined with WITH and used within a SQL statement.

Can a CTE be JOINed?

Yes. A CTE can be referenced like a table and JOINed with other tables or CTEs.

Have you finished this lesson?

Introduction to SQL – All Lessons