Common Table Expressions (CTE) Tutorial for Beginners: Essential SQL Skills

SQL queries can become messy and hard to read when dealing with complex data operations. Many developers struggle with long, nested subqueries that are difficult to understand and maintain. Common Table Expressions (CTEs) solve this problem by creating temporary result sets that make complex queries readable and organized.

CTEs act like temporary tables that exist only during query execution. They allow developers to break down complicated logic into smaller, manageable pieces. This approach makes debugging easier and helps team members understand the code faster.

This tutorial covers everything needed to master CTEs, from basic syntax to advanced recursive operations. Readers will learn how to use CTEs across different SQL platforms, work with hierarchical data, and apply best practices that improve query performance and maintainability.

Key Takeaways

What Is a Common Table Expression (CTE)?

A Common Table Expression (CTE) is a temporary result set that exists only during query execution. CTEs make complex SQL queries easier to read and write by breaking them into smaller, manageable parts.

CTE Fundamentals

A Common Table Expression defines a named temporary result set using the WITH clause. The CTE exists only while the main query runs and gets deleted after execution finishes.

The basic syntax follows this pattern:

SQL
WITH cte_name AS (
    SELECT statement
)
SELECT * FROM cte_name;

The WITH clause starts the CTE definition. After WITH, developers specify a name for the CTE. The AS keyword separates the name from the SELECT statement that creates the data.

CTEs work like virtual tables during query execution. They store data temporarily and allow the main query to reference this data by name. Multiple CTEs can be defined in a single query by separating them with commas.

SQL treats CTEs as if they were regular tables within the query scope. This means developers can join CTEs with other tables, filter their data, or use them in subqueries.

Temporary Result Sets Explained

CTEs create temporary result sets that live only during the current query execution. These result sets behave like tables but exist in memory rather than on disk.

When SQL executes a query with a CTE, it first runs the CTE definition to create the temporary data. The main query then uses this temporary result set as if it were a regular table.

Key characteristics of CTE temporary result sets:

The temporary nature makes CTEs perfect for organizing complex logic. Developers can break down complicated queries into steps without creating permanent tables.

CTEs can reference columns from their defining SELECT statement. The column names in the CTE come from the SELECT statement unless explicitly specified in the CTE definition.

CTEs vs Subqueries

CTEs and subqueries both help organize complex SQL queries, but they work differently. Subqueries are queries nested inside other queries, while CTEs define named temporary result sets.

Readability differences:

Performance considerations:

Reusability factors:

CTEs shine when queries need the same calculated data multiple times. Instead of repeating subqueries, developers define the logic once in a CTE and reference it as needed.

CTE Syntax and Structure

A CTE follows a specific structure that starts with the WITH clause, includes a query definition, and requires proper naming. The syntax is simple but must follow exact rules to work correctly.

WITH Clause Usage

The WITH clause starts every common table expression. It tells SQL that a temporary result set is about to be defined.

The basic syntax looks like this:

SQL
WITH cte_name AS (
    SELECT statement
)

The WITH clause must come right before the main query. It cannot be used in the middle of other SQL statements.

Multiple CTEs can be defined in one WITH clause. Each CTE gets separated by a comma:

SQL
WITH first_cte AS (
    SELECT ...
),
second_cte AS (
    SELECT ...
)

The WITH clause creates a named result set that exists only during query execution. Once the query finishes, the CTE disappears.

CTE Query Definition

The CTE query definition contains the SELECT statement that creates the temporary result set. This query goes inside parentheses after the AS keyword.

The query definition follows most normal SELECT statement rules. It can include WHERE clauses, JOIN operations, and GROUP BY statements.

Basic structure:

The query definition cannot use certain SQL features. ORDER BY only works with TOP or OFFSET clauses. The INTO clause is not allowed.

Column matching is important. If column names are specified in the CTE definition, the number must match the SELECT statement results exactly.

Naming Conventions

CTE names must be unique within the same WITH clause. The name cannot match other CTEs in the same statement.

Valid naming rules:

Column names can be specified after the CTE name. This is optional if the SELECT statement already provides clear column names:

SQL
WITH sales_data (person_id, total_sales, year) AS (
    SELECT SalesPersonID, SUM(TotalDue), YEAR(OrderDate)
    FROM Sales.SalesOrderHeader
)

The CTE name gets used in the main query just like a table name. It must be referenced exactly as defined in the WITH clause.

Practical Applications of CTEs

CTEs transform how data analysts write and manage SQL queries by breaking down complex operations into manageable, named components. They replace nested subqueries with clear, step-by-step logic that anyone can follow.

Improving Query Readability

SQL CTEs make queries easier to read and understand. Instead of complex nested subqueries, analysts can break logic into named sections.

A traditional query with subqueries looks messy:

SQL
SELECT customer_id, order_total 
FROM orders o
WHERE order_total > (SELECT AVG(order_total) FROM orders WHERE store_id = o.store_id)

The same query with a CTE is much clearer:

SQL
WITH store_averages AS (
    SELECT store_id, AVG(order_total) AS avg_total
    FROM orders
    GROUP BY store_id
)
SELECT o.customer_id, o.order_total
FROM orders o
JOIN store_averages sa ON o.store_id = sa.store_id
WHERE o.order_total > sa.avg_total

CTEs work like variables in programming. Each CTE has a clear name that explains what data it contains. This helps other team members understand the query logic quickly.

Simplifying Complex Queries

Complex queries with multiple joins become manageable with CTEs. Data analysts can build results step by step instead of writing one giant query.

Consider calculating sales performance across regions:

SQL
WITH monthly_sales AS (
    SELECT region, DATE_TRUNC('month', order_date) AS month, 
           SUM(amount) AS total_sales
    FROM orders
    GROUP BY region, DATE_TRUNC('month', order_date)
),
region_averages AS (
    SELECT region, AVG(total_sales) AS avg_monthly_sales
    FROM monthly_sales
    GROUP BY region
)
SELECT ms.region, ms.month, ms.total_sales, ra.avg_monthly_sales
FROM monthly_sales ms
JOIN region_averages ra ON ms.region = ra.region

Each CTE handles one calculation. The first finds monthly sales totals. The second calculates regional averages. The main query combines both results.

This approach prevents errors and makes debugging easier. If something goes wrong, analysts can test each CTE separately.

Working with Multiple and Nested CTEs

You can combine multiple CTEs in a single query using commas to separate them, while nested CTEs allow one CTE to reference another for more complex data processing.

Defining Multiple CTEs

Multiple CTEs let you break complex queries into smaller, manageable parts. You write them using specific syntax rules that must be followed exactly.

Key syntax rules:

Here’s the basic structure:

SQL
WITH first_cte AS (
    SELECT column1, column2
    FROM table1
),
second_cte AS (
    SELECT column3, column4
    FROM table2
)
SELECT *
FROM first_cte, second_cte;

The CTEs can work independently or reference each other. Independent CTEs pull data from different sources without connecting to other CTEs in the query.

When one CTE references another, you create a chain of operations. The second CTE can use data from the first CTE like it’s a regular table.

Nested CTE Examples

Nested CTEs happen when one CTE references data from a previous CTE. This creates powerful data processing chains for complex calculations.

Here’s a practical example:

SQL
WITH sales_data AS (
    SELECT product_id, SUM(sales) as total_sales
    FROM orders
    GROUP BY product_id
),
sales_with_bonus AS (
    SELECT product_id, 
           total_sales,
           total_sales * 0.1 as bonus
    FROM sales_data
)
SELECT * FROM sales_with_bonus;

The second CTE sales_with_bonus uses data from the first CTE sales_data. This makes complex calculations easier to read and debug.

Important rules for nested CTEs:

Nested CTEs work great for step-by-step data transformations where each step builds on the previous one.

Recursive CTEs and Hierarchical Data

Recursive CTEs use a special structure with anchor and recursive members to process hierarchical data like employee organization charts or family trees. The WITH clause creates these self-referencing queries that repeatedly execute until they process all levels of the hierarchy.

Recursive Query Structure

A recursive CTE follows a specific pattern that includes three essential parts. The query must contain at least one anchor member and one recursive member connected by UNION ALL.

SQL
WITH cte_name (column_list)
AS (
    -- Anchor member (base case)
    SELECT columns FROM table WHERE condition
    UNION ALL
    -- Recursive member (references itself)
    SELECT columns FROM table 
    INNER JOIN cte_name ON join_condition
)
SELECT * FROM cte_name;

The WITH clause begins the recursive CTE definition. The anchor member runs first and creates the starting result set. The recursive member then references the CTE name itself to create the recursive behavior.

MAXRECURSION controls how many times the query can repeat itself. SQL Server sets a default limit of 100 recursions to prevent infinite loops. Users can override this by adding OPTION (MAXRECURSION number) to their query.

Anchor Member and Recursive Member Explained

The anchor member defines the starting point for recursive queries. It runs only once and creates the base result set without referencing itself. This member typically finds root-level records such as top managers in an organization.

SQL
-- Anchor member example
SELECT EmployeeID, Name, ManagerID, 0 as Level
FROM Employees 
WHERE ManagerID IS NULL  -- Find CEO/top level

The recursive member references the CTE name to create the recursive behavior. It joins the original table with the CTE results from the previous iteration. This member continues executing until it returns no new rows.

SQL
-- Recursive member example  
SELECT e.EmployeeID, e.Name, e.ManagerID, d.Level + 1
FROM Employees e
INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID

Each iteration processes one level deeper in the hierarchy. The recursion stops automatically when no more matching records exist.

Hierarchical Data Modeling

Hierarchical data represents parent-child relationships where each record can have one parent and multiple children. Common examples include employee reporting structures, product categories, and file system directories.

Recursive CTEs excel at traversing these tree-like structures. They can move down from parent to child or up from child to parent depending on the join conditions used.

Data TypeParent ColumnChild ColumnUse Case
EmployeesManagerIDEmployeeIDOrg charts
CategoriesParentCategoryIDCategoryIDProduct hierarchy
LocationsParentLocationIDLocationIDGeographic structure

The recursive approach processes one level at a time. Level 0 contains root records, Level 1 contains their direct children, Level 2 contains grandchildren, and so on.

Recursive queries handle complex hierarchical operations that would require multiple self-joins or cursor-based solutions in traditional SQL. They provide a clean, readable way to work with tree structures of unknown depth.

Using CTEs with Different SQL Statements

CTEs work with SELECT statements to create temporary result sets for complex queries. They also work with INSERT, UPDATE, and DELETE statements to modify data based on temporary calculations.

SELECT Statements

CTEs are most commonly used with SELECT statements to break down complex queries into smaller parts. The CTE creates a temporary named result set that the main SELECT statement can reference.

A basic CTE with SELECT starts with the WITH keyword. The CTE gets a name and contains a SELECT statement that defines the temporary data.

SQL
WITH sales_totals AS (
    SELECT customer_id, SUM(amount) as total_sales
    FROM orders
    GROUP BY customer_id
)
SELECT c.customer_name, st.total_sales
FROM customers c
JOIN sales_totals st ON c.customer_id = st.customer_id;

Multiple CTEs can be used in one SELECT statement. Each CTE is separated by a comma after the WITH keyword.

CTEs can reference other CTEs in the same query. This creates nested CTEs that build on each other’s results.

INSERT, UPDATE, and DELETE Statements

CTEs work with data modification statements to perform complex operations. The CTE calculates the data needed for the INSERT, UPDATE, or DELETE operation.

For INSERT statements, CTEs can prepare data before adding it to a table:

SQL
WITH new_employees AS (
    SELECT name, department, salary * 1.1 as adjusted_salary
    FROM temp_hires
    WHERE hire_date = '2025-01-01'
)
INSERT INTO employees (name, department, salary)
SELECT name, department, adjusted_salary
FROM new_employees;

UPDATE statements use CTEs to calculate new values:

SQL
WITH bonus_calc AS (
    SELECT employee_id, salary * 0.05 as bonus_amount
    FROM employees
    WHERE performance_rating > 4
)
UPDATE employees 
SET bonus = bc.bonus_amount
FROM bonus_calc bc
WHERE employees.employee_id = bc.employee_id;

DELETE statements can use CTEs to identify which rows to remove based on complex conditions.

CTEs in Major SQL Platforms

Different SQL databases implement CTEs with varying features and syntax requirements. SQL Server offers full CTE support including recursive queries, while PostgreSQL provides comprehensive CTE functionality with additional optimization features.

CTEs in SQL Server

SQL Server provides complete CTE support through the WITH clause. CTEs work in SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

The basic syntax requires the WITH keyword followed by the CTE definition:

SQL
WITH Sales_CTE (SalesPersonID, TotalSales)
AS
(
    SELECT SalesPersonID, SUM(TotalDue)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT * FROM Sales_CTE;

Recursive CTEs are fully supported in SQL Server. They require an anchor member and recursive member joined by UNION ALL.

Multiple CTEs can be defined in a single query using commas to separate definitions. Each CTE must have a unique name within the same WITH clause.

SQL Server requires a semicolon before the WITH clause when the CTE statement is part of a batch. This prevents parsing errors.

CTEs in PostgreSQL

PostgreSQL offers robust CTE support with the WITH clause. The database supports both recursive and non-recursive CTEs with advanced features.

PostgreSQL allows materialized CTEs using WITH MATERIALIZED. This forces the CTE to be computed once and stored temporarily:

SQL
WITH MATERIALIZED sales_data AS (
    SELECT region, SUM(amount) as total
    FROM sales
    GROUP BY region
)
SELECT * FROM sales_data;

The database also supports NOT MATERIALIZED to prevent materialization when PostgreSQL would normally choose to materialize.

Recursive CTEs in PostgreSQL follow standard SQL syntax. They support complex recursive operations like tree traversal and graph analysis.

PostgreSQL CTEs can reference other CTEs defined in the same WITH clause. Forward references are not allowed, similar to other SQL platforms.

Essential Tips, Best Practices, and Limits

CTEs require careful attention to performance factors and come with specific limitations that can impact query execution. Understanding these constraints helps developers write more efficient SQL code.

Performance Considerations

CTEs can affect query performance in several ways. Unlike temporary tables, CTEs exist only during query execution and cannot be indexed.

Query Optimization Tips:

CTEs may be executed multiple times if referenced more than once in the main query. This can slow down performance with large datasets.

For recursive CTEs, SQL Server uses the MAXRECURSION option to prevent infinite loops. The default limit is 100 recursion levels.

SQL
WITH RecursiveCTE AS (...)
SELECT * FROM RecursiveCTE
OPTION (MAXRECURSION 500);

CTEs improve query readability by breaking complex logic into smaller parts. However, this benefit should be balanced against potential performance costs.

Limitations and Common Pitfalls

CTEs have several important restrictions. They cannot be indexed and are not reusable across multiple statements.

Key Limitations:

Common mistakes include creating overly complex CTEs that hurt performance. Developers sometimes use CTEs when simpler solutions exist.

Recursive CTEs can cause infinite loops without proper termination conditions. Always include a stopping condition in the recursive part of the query.

CTE names must be unique within the same WITH clause. Multiple CTEs require comma separation between definitions.

Frequently Asked Questions

CTEs provide temporary result sets that exist only during query execution. They offer advantages over subqueries through better readability and reusability, while differing from temporary tables by not creating persistent database objects.

What is a Common Table Expression and how is it used in SQL?

A Common Table Expression is a temporary result set defined within a SQL query using the WITH clause. It creates a named subquery that can be referenced multiple times in the main query.

The basic syntax starts with WITH followed by a name and query definition. The main SELECT statement then references the CTE by name as if it were a table.

CTEs make complex queries easier to read and maintain. They break down large queries into smaller, manageable parts without creating permanent database objects.

The database engine executes the CTE first, stores the intermediate results, then uses them to build the final output. Once the query completes, the CTE disappears completely.

How does one structure a SQL query with multiple CTEs?

Multiple CTEs are separated by commas within the same WITH clause. Each CTE gets its own name and query definition before the main SELECT statement.

CTEs can reference previously defined CTEs in the same query through nesting. This allows developers to build solutions step by step, with each CTE solving part of the problem.

The structure follows this pattern: WITH cte1 AS (query1), cte2 AS (query2) SELECT. The main query can then join or reference any of the defined CTEs.

Nested CTEs help decompose complex logic into manageable pieces. Each CTE can use results from earlier CTEs to gradually reach the final solution.

In what scenarios is it better to use a CTE over a temporary table?

CTEs work better when the temporary data is only needed for a single query execution. They don’t create persistent objects that take up database space or require cleanup.

Temporary tables are better for data that needs to be accessed multiple times across different queries or sessions. They also work better for very large result sets that might benefit from indexing.

CTEs execute faster for simple operations since they don’t involve creating physical storage. They also provide better query organization and readability compared to temporary tables.

Use CTEs when breaking down complex queries into readable parts. Choose temporary tables when the intermediate data needs to persist beyond one query or requires performance optimization through indexes.

Can you compare the use of Common Table Expressions versus subqueries?

CTEs appear at the beginning of queries in the WITH clause, making them easier to read and understand. Subqueries are embedded within the main query in parentheses, which can make complex queries harder to follow.

A CTE can be referenced multiple times within the same query using its assigned name. Subqueries must be rewritten each time they’re needed, leading to code duplication.

CTEs allow for better query organization by separating logic into named components. This makes debugging and maintenance easier compared to nested subqueries.

Both approaches produce similar performance results. The choice often depends on readability requirements and whether the temporary result set needs multiple references.

What are the benefits of using CTEs in PostgreSQL?

PostgreSQL supports recursive CTEs with the RECURSIVE keyword, enabling traversal of hierarchical data structures. This eliminates the need for complex procedural code to handle tree-like data.

CTEs in PostgreSQL improve query performance by allowing the optimizer to better understand query structure. They also make complex analytical queries more maintainable.

PostgreSQL CTEs support both read and write operations. They can be used with SELECT, INSERT, UPDATE, and DELETE statements for flexible data manipulation.

The database provides excellent optimization for CTE queries, often producing execution plans comparable to equivalent subqueries or temporary tables.

Is Common Table Expression functionality available in MySQL, and if so, how does it work?

MySQL introduced support for Common Table Expressions (CTEs) starting with version 8.0. The syntax uses the WITH clause, followed by one or more named query definitions.

CTEs in MySQL support both non-recursive and recursive operations. Recursive CTEs utilize the WITH RECURSIVE syntax to enable hierarchical data processing.

You can define multiple CTEs in a single query, separating each with a comma. Each CTE can reference any previously defined CTEs within the same WITH clause.

MySQL manages CTE execution by either materializing the results or treating them as derived tables, depending on which approach is more efficient. This helps ensure good performance for most use cases.

Leave a Reply

Your email address will not be published. Required fields are marked *

Let's work together

support@analyticsengineering.com

Hire Analytics Engineering Talent

vertexdataconsulting.com

© Analytics Engineering 2025. All rights reserved.

Facebook-f Jki-instagram-1-light X-twitter Linkedin-in