Beginner

519. JOIN with CTE

Learn how Common Table Expressions can simplify JOIN queries and organize complex SQL logic.

📘 Lesson 519 of 728 ⏱ 13 min 🎯 Intermediate

A Common Table Expression, or CTE, is a named temporary result set defined using the WITH clause and available to the statement that follows it.

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

Calculate total sales by customer in a CTE and then join those totals 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
INNER JOIN CustomerSales cs
    ON c.CustomerID = cs.CustomerID;

Common in analytics, reporting, transformations, recursive queries, and complex business logic.

  • CTEs are defined using WITH.
  • A CTE can simplify complex JOIN logic.
  • CTEs improve readability.
  • CTEs can contain aggregation and filtering.
  • A CTE exists for the duration of the statement.
What is a CTE?

A CTE is a named temporary result set defined with the WITH clause for use within a SQL statement.

Why use a CTE with JOIN?

A CTE can separate complex preparation or aggregation logic from the final JOIN, making the query easier to understand.

Have you finished this lesson?

Introduction to SQL – All Lessons