← Back to Curriculum

Common Table Expressions (CTEs)

📚 Lesson 10 of 15 ⏱️ 40 min

Common Table Expressions (CTEs)

40 min

Common Table Expressions (CTEs) are temporary named result sets that exist only within the execution scope of a single SQL statement. CTEs are defined using the `WITH` clause and can be referenced multiple times in the same query. CTEs improve query readability by breaking complex queries into manageable, named parts. Understanding CTEs enables you to write cleaner, more maintainable SQL. CTEs are available in modern SQL databases (PostgreSQL, SQL Server, MySQL 8.0+, Oracle).

CTEs are defined with `WITH cte_name AS (SELECT ...)` and then referenced in the main query. CTEs can be used in SELECT, INSERT, UPDATE, DELETE, and CREATE VIEW statements. CTEs are evaluated once and can be referenced multiple times, making them useful for avoiding repeated subqueries. Understanding CTE syntax enables you to use them effectively. CTEs are similar to derived tables but more readable.

CTEs improve query readability by allowing you to name intermediate results and break complex queries into logical steps. Instead of nested subqueries, you can define CTEs that represent logical parts of your query. This makes queries easier to understand, debug, and maintain. Understanding CTEs as a readability tool helps you write better SQL. CTEs don't improve performance—they're primarily for organization.

Recursive CTEs enable queries that reference themselves, making them powerful for hierarchical data like organizational charts, file systems, or category trees. Recursive CTEs consist of an anchor member (base case) and a recursive member (recursive case). The recursive member references the CTE itself, building results iteratively. Understanding recursive CTEs enables you to query hierarchical structures. Recursive CTEs require careful design to avoid infinite loops.

CTEs can help break down complex queries into more manageable pieces. Instead of deeply nested subqueries, you can define multiple CTEs that each represent a logical step. The main query then combines these CTEs in a clear, readable way. Understanding CTEs as a decomposition tool helps you tackle complex queries. CTEs can reference other CTEs defined earlier in the same WITH clause.

Best practices include using CTEs for readability, avoiding overuse (sometimes subqueries are simpler), understanding that CTEs don't improve performance, using meaningful CTE names, and testing recursive CTEs carefully to avoid infinite loops. Understanding CTEs enables you to write cleaner, more maintainable SQL queries.

Key Concepts

  • CTEs are temporary named result sets defined with WITH.
  • CTEs exist only within the execution scope of a single statement.
  • CTEs improve query readability by breaking complex queries into parts.
  • Recursive CTEs enable queries on hierarchical data.
  • CTEs can be referenced multiple times in the same query.

Learning Objectives

Master

  • Creating CTEs with the WITH clause
  • Using CTEs to improve query readability
  • Writing recursive CTEs for hierarchical data
  • Referencing CTEs multiple times in queries

Develop

  • Query organization thinking
  • Understanding hierarchical data structures
  • Writing maintainable, readable SQL

Tips

  • Use CTEs to break complex queries into logical, named parts.
  • Use meaningful names for CTEs to improve readability.
  • Test recursive CTEs carefully to avoid infinite loops.
  • Remember that CTEs don't improve performance—they're for organization.

Common Pitfalls

  • Overusing CTEs when simple subqueries would suffice.
  • Creating infinite loops in recursive CTEs.
  • Expecting CTEs to improve performance (they don't).
  • Not using meaningful CTE names, reducing readability benefits.

Summary

  • CTEs are temporary named result sets that improve query readability.
  • CTEs are defined with WITH and can be referenced multiple times.
  • Recursive CTEs enable queries on hierarchical data.
  • Understanding CTEs enables cleaner, more maintainable SQL.
  • CTEs are primarily for organization, not performance.

Exercise

Create a CTE to find the average order value and then use it to find orders above average.

WITH OrderStats AS (
    SELECT AVG(TotalAmount) as AvgOrderValue
    FROM Orders
)
SELECT OrderID, CustomerID, TotalAmount
FROM Orders, OrderStats
WHERE TotalAmount > OrderStats.AvgOrderValue;

Code Editor

Output