Common Table Expression (CTE) in SQL

What is Common Table Expression (CTE) in SQL

Jaya
November 13th, 2026
55
5:00 Minutes

Are there times when you have to look at an old SQL query and have no idea what to do after five nested subqueries? A lot of programmers face this issue.

SQL CTEs help overcome this challenge by breaking large, complicated queries into smaller, manageable parts

Let us see what CTE is in SQL. In this article, we will review the meaning, syntax and types of CTEs. You will also find examples of CTEs for SQL that may be performed in your own database.

What Is CTE in SQL?

A Common Table Expression (CTE) in SQL is a temporary, named query result that can be used within a single SQL statement, such as SELECT, INSERT, UPDATE or DELETE.

Think of a SQL CTE as a short note you write before the main query. The note gives a name to a piece of logic. Then the main query uses that name instead of repeating the logic.

Here are the key facts about a CTE:

  • It exists only while the statement runs.
  • It does not store data on disk.
  • It works with SELECT, INSERT, UPDATE and DELETE statements.
  • It can refer to itself, which makes recursive queries possible.

The SQL standard added CTEs in SQL:1999. Microsoft SQL Server added support in version 2005. Today, most modern databases support them.

Master SQL. Turn Data Into Insights.

Learn SQL queries, database management, data manipulation, joins, and advanced functions through practical training.

Enroll in SQL Training →

SQL CTE Syntax: How the SQL WITH Clause Works

The SQL WITH clause starts every CTE. Here is the basic syntax:

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

Let us look at each part:

  1. WITH tells the database that a CTE definition follows.
  2. cte_name is the name you choose. It must be unique inside the statement.
  3. (column1, column2) is an optional column list. You can skip it when the query already names its columns.
  4. AS ( ... ) holds the query that builds the result set.
  5. The main query comes right after the CTE. It reads from the CTE like it reads from a table.

One rule matters a lot. The CTE is valid only for the statement that follows it. If you run a second statement, the database will not find the CTE. You must define it again.

CTE in SQL With Example

Examples make the idea clear. We will use a simple employees table for all queries in this guide.

emp_idemp_namedepartmentsalarymanager_id
1AshaManagement120000NULL
2RaviSales700001
3MeeraSales900002
4KabirIT950001
5NehaIT800004
6ArjunIT1050004

Example 1: Find employees who earn more than their department average

Without a CTE, you would write a subquery inside a join. With a CTE, the query reads in two clear steps.

WITH dept_avg AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT e.emp_name, e.department, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d
ON e.department = d.department
WHERE e.salary > d.avg_salary;

Step one builds dept_avg, which holds the average salary for each department. Step two joins that result to the employees table and keeps only the higher earners.

The query is easy to read because the name dept_avg explains the purpose of the logic.

Example 2: Use multiple CTEs in one query

You can define more than one CTE after a single WITH keyword. Separate each definition with a comma. A later CTE can also use an earlier one.

WITH dept_totals AS (
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
),
company_total AS (
SELECT SUM(total_salary) AS grand_total
FROM dept_totals
)
SELECT d.department,
d.total_salary,
ROUND(d.total_salary * 100.0 / c.grand_total, 2) AS pct_of_company
FROM dept_totals d
CROSS JOIN company_total c;

Here, company_total reads from dept_totals. This chaining keeps each step small. It also helps you test each step on its own.

Master SQL Server. Build Database Expertise.

Learn SQL Server database management, queries, stored procedures, performance tuning, and data administration through practical training.

Enroll in SQL Server Training →

Types of CTE in SQL

There are two main types of Common Table Expressions (CTEs) in SQL:

1. Non-Recursive CTE

  • A Non-Recursive CTE does not call or refer to itself.
  • It retrieves data using a regular SQL query.
  • It creates a temporary named result set that can be used in the main query.
  • It makes complex SQL queries easier to read and manage.
  • It is commonly used for filtering, sorting, and organizing data.

2. Recursive CTE

  • A Recursive CTE refers to itself while executing a query.
  • It repeatedly retrieves related data until a specified condition is met.
  • It consists of two main parts:
  • It is commonly used to retrieve hierarchical and parent-child relationships.
  • Examples include employee reporting structures, organizational charts, and category hierarchies.

Difference Between Recursive and Non-Recursive CTE

  • Non-Recursive CTE: Executes a regular query without referring to itself.
  • Recursive CTE: Refers to itself to retrieve data through multiple iterations.

Note: Single CTEs and Multiple CTEs are structural variations, not separate fundamental types of CTEs.

Recursive CTE in SQL

A recursive CTE calls itself until it runs out of rows. It is the best tool for hierarchical data, such as org charts, category trees and folder structures.

A recursive CTE has two parts:

  • Anchor member: the starting rows. It runs once.
  • Recursive member: the query that references the CTE name. It runs again and again until no new rows appear.

The two parts join with UNION ALL. Here is an org chart example:

WITH RECURSIVE org_chart AS (
-- Anchor: the top manager
SELECT emp_id, emp_name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: employees who report to someone already found
SELECT e.emp_id, e.emp_name, e.manager_id, o.level + 1
FROM employees e
JOIN org_chart o
ON e.manager_id = o.emp_id
)
SELECT emp_name, level
FROM org_chart
ORDER BY level;

The anchor finds Asha, who has no manager. The recursive part then finds the people who report to Asha. It repeats the step for each new level.

Note: PostgreSQL, MySQL and SQLite need the RECURSIVE keyword. SQL Server and Oracle do not use it. Write WITH org_chart AS (...) in those systems.

Always add a stop condition or a level limit when your data might contain loops. Otherwise, the query can run for a very long time.

Also Explore: SQL Interview Questions and Answers

CTE vs Subquery vs View vs Temp Table

New learners often mix these four tools. The table below shows the main differences.

FeatureCTESubqueryViewTemp Table
LifetimeOne statementOne statementSaved in the databaseOne session
Reusable in the same queryYesUsually noYesYes
Supports recursionYesNoNoNo
Stores dataNoNoNo (stores the query only)Yes
Best forReadable, step-by-step logicShort, simple filtersShared logic across queriesLarge or repeated intermediate results

Use a CTE when you want clear code for a single query. Use a view when many queries need the same logic. Use a temp table when you need to reuse a large result several times or add an index to it.

Master SQL for Data Science. Turn Data Into Insights.

Learn SQL querying, data extraction, database management, and data analysis techniques for real-world data science applications.

Benefits of Using a SQL CTE

A SQL CTE offers several practical benefits:

  • Better readability: Each step has a name, so the query reads like a story.
  • Easier maintenance: You can change one block without touching the rest.
  • Less repetition: You can reference the same CTE more than once in the main query.
  • Recursive power: You can walk through trees and hierarchies without loops or extra code.
  • Simpler debugging: You can run each CTE alone to check its output.

Limitations of CTEs

A CTE is helpful, but it is not perfect. Keep these points in mind:

  • It lives for one statement only: You cannot reuse it in the next query.
  • It does not always improve speed: A CTE is not automatically materialized or stored as a temporary result. SQL Server may re-execute its definition when referenced multiple times, depending on the execution plan. In PostgreSQL 12 and later, the optimizer can inline a simple CTE that you use once.
  • You cannot index it: If you need an index, use a temp table.
  • Deep recursion has limits: SQL Server stops at 100 recursion levels by default. You can change this with the MAXRECURSION option.

Read Also: Top SQL Server Interview Questions and Answers

Best Practices for Writing a CTE in SQL

Follow these tips to write clean and fast CTEs:

  1. Use clear names: Pick names like dept_avg or active_users instead of cte1.
  2. Keep each CTE focused: One CTE should do one job.
  3. Select only the columns you need: Smaller result sets run faster.
  4. Filter early. Add WHERE conditions inside the CTE to reduce rows sooner.
  5. Do not chain too many CTEs: A long chain can become hard to read. Split the work or use a temp table when needed.
  6. Check the execution plan: Do not assume a CTE will run faster than a subquery. Test it on your data.
  7. Limit recursion: Add a level counter to protect against infinite loops.

Which Databases Support CTEs?

Most major databases support the SQL WITH clause. These include:

  • PostgreSQL
  • MySQL (version 8.0 and later)
  • SQL Server (version 2005 and later)
  • Oracle
  • SQLite (version 3.8.3 and later)
  • IBM Db2
  • Snowflake and BigQuery

Older versions of MySQL do not support CTEs. If you use MySQL 5.7, you must upgrade or use subqueries instead.

Final Thoughts

Now you know what CTE in SQL means and how it works. A common table expression in SQL gives your query a clean structure. The SQL WITH clause lets you name each step, reuse logic and handle hierarchical data with recursion.

Start small. Take one query with a nested subquery and rewrite it as a SQL CTE. You will see the difference in readability right away. Then try a recursive CTE on your own data. With a little practice, CTEs will become a daily habit in your SQL work.

Explore our Trending Articles:-

FAQs

1. Is a CTE faster than a subquery?

Not always. In most cases, the database handles both in a similar way. A CTE mainly improves readability. Always test both options with your own data.

2. Can I use a CTE with INSERT, UPDATE, or DELETE?

Yes. You can place a CTE before any of these statements. Support and syntax differ by database, so check your database documentation first.

3. Is a CTE the same as a temporary table?

No. A temporary table stores data and lasts for a session. A CTE does not store data and lasts for one statement.

4. Can We use more than one CTE in a query?

Yes. Write one WITH keyword and separate each CTE with a comma. Later CTEs can use earlier ones.

Want to learn more? Keep reading:
Ready to go from reading to doing?
About the Author
Jaya | igmGuru
About the Author

Jaya is a big data professional with experience building large-scale data pipelines and helping teams move from spreadsheet reporting to distributed systems such as Hadoop and Spark. Jaya's expertise includes data engineering, schema management, and cluster performance tuning. She has been working with us for a long time and loves to write well-researched content pieces for our learners.

Drop Us a Query
Fields marked * are mandatory
×

Your Shopping Cart


Your shopping cart is empty.