Course contentsShow
← Data Science
Lesson 989 of 2,14525. SQL Subqueries and CTEsFree lesson

What are Common Table Expressions (CTEs)?

Understanding CTEs as named temporary result sets defined with WITH clause for improving query readability.

What are Common Table Expressions (CTEs)?

You'll learn how CTEs provide a cleaner, more readable way to organize complex queries by naming temporary result sets.

The Core Idea

A Common Table Expression (CTE) is a named temporary result set that you define at the beginning of a query using the WITH clause. Think of it as creating a named "scratch pad" that exists only for the duration of your query. Instead of cramming subqueries into the middle of your FROM or WHERE clauses (which you've already learned), you can define them up front with descriptive names.

It's like giving a nickname to a complex calculation before using it — the same way you might say "Let's call the eligible customers 'active_buyers'" before diving into your main analysis.

Why CTEs Matter

CTEs make your SQL more readable and maintainable. Compare these two approaches:

Without CTE (nested subquery in FROM): The subquery sits in the middle of your main query, making it harder to understand the logic flow.

With CTE: You define the temporary result set at the top with a clear name, then reference it by name in your main query. Your code reads more like a story: "First, I'll identify X, then I'll use X to calculate Y."

CTE vs Derived Tables

You've already worked with subqueries in the FROM clause (derived tables). CTEs serve the same purpose but with better readability. They're especially powerful when you need to reference the same subquery multiple times — with CTEs, you define it once and reuse the name.

Key Takeaway: CTEs are named temporary result sets defined with the WITH clause that improve query organization and readability, offering a cleaner alternative to nested subqueries.