Beginner

477. JOIN with CTE

Learn how Common Table Expressions can prepare data before joining it with another table.

📘 Lesson 477 of 728 ⏱ 13 min 🎯 Intermediate

A Common Table Expression, or CTE, is a named temporary result set defined using WITH that can be referenced by the main query.

CTEs make complex SQL easier to read, organize, test, and maintain.

Calculate customer sales in a CTE and then join the result to customer information.

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

CTEs are widely used in analytical reporting, transformation logic, data-quality checks, and complex business queries.

  • CTEs are defined using WITH.
  • A CTE can be joined like a result set.
  • CTEs improve query readability.
  • CTEs can separate complex logic into steps.
  • CTEs exist for the duration of the query.
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 used in a JOIN?

Yes. A CTE can be referenced like a table and joined with other tables.

Have you finished this lesson?

Introduction to SQL – All Lessons