Common Table Expression (CTE)

Long SQL queries do not have to be confusing.

A common table expression (CTE) helps you name a temporary result and use it like a table inside a larger query. You define it once, use it multiple times, and keep your logic simple and easy to follow.

CTEs are useful when working with recursive data, running joins, or repeating the same calculation across different parts of a query.

Instead of repeating subqueries, CTEs let you split your logic into clear steps. They run once, last only for that query, and disappear after.

They make complex SQL easier to write and understand.

‍

What Is a Common Table Expression?

A common table expression is a temporary result you define at the start of a SQL query.

It starts with the WITH keyword, which lets you name a table expression. That result works like a table during the query but is not stored afterward.

This helps in two ways:

• It keeps your SQL cleaner and easier to manage
• It lets you reuse logic more than once in the same query

Here is a simple example:

‍

‍

In this case, recent_orders is a temporary table you can use in your main query. You define it once and use it as many times as needed.

CTEs work with SELECT, INSERT, UPDATE, DELETE, and MERGE. You can use them for reports, data changes, or recursive problems.

Recursive CTEs are more advanced. They can reference themselves and help with tasks like walking through parent-child data. To stay safe, most systems let you set a limit with MAXRECURSION so your query does not get stuck in a loop.

‍

Why Use a CTE Instead of a Subquery?

Subqueries work, but they can get messy.

When you repeat the same logic in different places, your query gets long and hard to read. With a CTE, you define that logic once and give it a name.

This makes it easier to debug and maintain your SQL.

CTEs work great when:

• teams should do the same calculation more than once
• You are joining multiple layers of data
• You are breaking a big task into small steps

Because the CTE is defined at the top of your query, it is easy to see what it does. It will not affect your database or your schema. It just runs, gives a result, and disappears.

‍

How a Common Table Expression Works

Every CTE starts with WITH.

You give it a name, define the columns (optional), and write a query that returns rows. This result is temporary and only used in the rest of your query.

‍

‍‍



You can also create more than one CTE. Just separate them with commas.


‍


You can join CTEs, add filters, or use them in calculations. You can use them with any SQL operation.

Recursive CTEs have two parts: a starting part and a repeating part. They build one layer at a time.

‍

‍


This helps with tree data, like categories or reporting structures. Be sure to use a stop condition or a limit so it does not run forever.

When to Use a Common Table Expression

CTEs are helpful when your SQL gets complicated.

Use them when:

• teams should run the same subquery more than once
• Your query is getting too long or too hard to follow
• teams should split a problem into smaller steps
• You are working with recursive or tree-style data
• You want to stage data before updating or deleting

For example, if you are doing totals, filters, and joins all in one query, it can be hard to follow. CTEs let you break it down step by step.

They also help when you want to apply changes to your data based on a temporary result set.

Recursive Common Table Expressions

Recursive CTEs let you work with data that has layers.

Think of employee-to-manager charts, file folders, or product parts. A recursive CTE starts with a base case and builds layer by layer using the result of the last step.

‍

‍

‍


Use a WHERE clause or a setting like MAXRECURSION to avoid infinite loops.

Real-World Examples

1. Reporting sales and targets

‍

‍

‍




2. Chaining logic in steps

‍

3. Updating based on a CTE

‍

‍

4. Using a CTE instead of a temp table

‍

FAQ

What is a CTE?

It is a temporary result created using WITH in SQL. You can reuse it in the same query.

Can I create more than one CTE?

Yes. Just separate them with commas.

Are CTEs stored in the database?

No. They only exist while the query is running.

What is a recursive CTE?

A CTE that calls itself to work with layered data like trees or hierarchies.

How do I stop a recursive CTE from running forever?

Use a WHERE clause or set a MAXRECURSION limit.

Which SQL statements work with CTEs?

CTEs work with SELECT, INSERT, UPDATE, DELETE, and MERGE.

Can I use joins, filters, and groups inside a CTE?

Yes. You can treat it like any other subquery.

Can a CTE reference another CTE?

Yes, as long as the one being referenced appears first.

Do all databases support CTEs?

Most do, including SQL Server, PostgreSQL, MySQL 8 and later, Oracle, and Snowflake.

Key Points

A common table expression (CTE) helps you write SQL that is clean and easy to follow. You can reuse logic, stage complex steps, and work with recursive data all inside a single query.

It is perfect for:

• Breaking down hard queries
• Avoiding repeated subqueries
• Working with trees and hierarchies
• Making your SQL easier to read and update

If your query is doing more than pulling a few columns, try using a CTE. It will help you stay organized and write better SQL.

‍