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:

‍

WITH recent_orders AS (
  SELECT order_id, customer_id
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT customer_id, COUNT(*) 
FROM recent_orders
GROUP BY customer_id;

‍

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:

• You need to 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.

‍

WITH cte_name (column1, column2) AS (
  SELECT column1, column2
  FROM some_table
  WHERE condition = true
)
SELECT column1
FROM cte_name;

‍‍



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

WITH filtered_data AS (
  SELECT * FROM sales WHERE region = 'West'
),
aggregated_data AS (
  SELECT salesperson, SUM(amount) AS total
  FROM filtered_data
  GROUP BY salesperson
)
SELECT * FROM aggregated_data WHERE total > 10000;


‍


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.

‍

WITH RECURSIVE hierarchy (id, parent_id, level) AS (
  SELECT id, parent_id, 1
  FROM org_chart
  WHERE parent_id IS NULL
  UNION ALL
  SELECT o.id, o.parent_id, h.level + 1
  FROM org_chart o
  JOIN hierarchy h ON o.parent_id = h.id
)
SELECT * FROM hierarchy;

‍


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:

• You need to run the same subquery more than once
• Your query is getting too long or too hard to follow
• You need to 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.

‍

‍

WITH RECURSIVE tree (id, parent_id, level) AS (
  SELECT id, parent_id, 1
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, t.level + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

‍


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

Real-World Examples

1. Reporting sales and targets

‍

‍

WITH total_sales AS (
  SELECT salesperson_id, SUM(amount) AS sales
  FROM sales
  GROUP BY salesperson_id
),
sales_quota AS (
  SELECT salesperson_id, target
  FROM quotas
)
SELECT 
  t.salesperson_id,
  t.sales,
  q.target,
  t.sales - q.target AS difference
FROM total_sales t
JOIN sales_quota q ON t.salesperson_id = q.salesperson_id;

‍




2. Chaining logic in steps

WITH recent_purchases AS (
  SELECT customer_id, product_id, purchase_date
  FROM purchases
  WHERE purchase_date >= CURRENT_DATE - INTERVAL '30 days'
),
purchase_counts AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM recent_purchases
  GROUP BY customer_id
)
SELECT *
FROM purchase_counts
WHERE order_count > 5;

‍

3. Updating based on a CTE

‍

WITH base_components AS (
  SELECT product_id, component_id
  FROM product_assemblies
  WHERE level = 0
)
UPDATE product_assemblies
SET quantity = quantity * 2
FROM base_components b
WHERE product_assemblies.component_id = b.component_id;

‍

4. Using a CTE instead of a temp table

‍

WITH temp_metrics AS (
  SELECT customer_id, AVG(order_total) AS avg_order
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM temp_metrics
WHERE avg_order > 100;

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.

Summary

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.

‍