Beginner

432. JOIN with CTE

Learn how Common Table Expressions can simplify complex JOIN queries.

📘 Lesson 432 of 728 ⏱ 12 min 🎯 Intermediate

A Common Table Expression, or CTE, is a temporary named result set defined using WITH that can be referenced within a SQL statement.

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

Create a customer sales summary using a CTE and join it with the Customers table.

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;

CTEs are widely used in reporting, analytics, data transformation, ranking, and complex SQL development.

  • CTEs are defined using WITH.
  • A CTE can be joined like a table.
  • CTEs improve query organization.
  • CTEs can contain aggregations and filtering.
  • CTEs exist for the duration of the statement.
What is a CTE?

A CTE is a temporary named result set defined using the WITH clause.

Why use a CTE with JOIN?

A CTE can separate complex calculations from the final JOIN, making the query easier to understand and maintain.

Have you finished this lesson?

Introduction to SQL – All Lessons