LearnContact
Lesson 6518 min read

Common Table Expressions (CTE)

Learn how to use WITH to define named, temporary result sets that make complex queries easier to read than nested subqueries.

Introduction

In the last lesson, you saw how subqueries let you nest one query inside another. Subqueries work well, but once you start nesting several of them together, queries can quickly become hard to read.

A Common Table Expression, or CTE, solves this by letting you name a temporary result set up front, and then reference that name later in the query as if it were a real table. This lesson shows how CTEs work and rewrites a subquery from the previous lesson using one.

What You Will Learn
  • What a CTE is and how the WITH clause works.
  • How to rewrite a subquery-based query as a CTE for better readability.
  • How to define multiple CTEs in a single query.
  • What a recursive CTE is, at a high level.

What is a CTE?

A Common Table Expression is a named, temporary result set, defined with a WITH clause, that exists only for the duration of the single query that follows it. Once that query finishes, the CTE is gone — it is not saved anywhere.

Think of it This Way

A CTE is like giving a subquery a name and pulling it up to the top of your SQL statement, so the rest of the query can simply refer to it by that name instead of being nested inside parentheses.

Basic CTE Syntax

A CTE starts with the WITH keyword, followed by a name you choose, the AS keyword, and the query in parentheses. The main query then follows and can reference that name just like a real table.

setup.sql
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    marks INT
);

INSERT INTO students VALUES
    (1, 'Alex', 82),
    (2, 'Priya', 91),
    (3, 'Sam', 58),
    (4, 'Riya', 70);
basic_cte.sql
WITH high_scorers AS (
    SELECT name, marks
    FROM students
    WHERE marks >= 70
)
SELECT * FROM high_scorers
ORDER BY marks DESC;
Result
+-------+-------+
| name  | marks |
+-------+-------+
| Priya | 91    |
| Alex  | 82    |
| Riya  | 70    |
+-------+-------+

Rewriting a Subquery as a CTE

Recall this query from the previous lesson, which used a subquery in the WHERE clause to find students scoring above average.

as_subquery.sql
-- The old, subquery-based version
SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

Using a CTE, you can name the average calculation and reference it clearly in the main query, which reads more naturally as the query grows more complex.

as_cte.sql
-- The same query, rewritten as a CTE
WITH average_marks AS (
    SELECT AVG(marks) AS avg_marks
    FROM students
)
SELECT s.name, s.marks
FROM students s, average_marks
WHERE s.marks > average_marks.avg_marks;
Result
+-------+-------+
| name  | marks |
+-------+-------+
| Alex  | 82    |
| Priya | 91    |
+-------+-------+
Same Result, Clearer Structure

Both versions produce identical results. The CTE version separates "calculate the average" from "compare against the average" into two clearly named, readable steps instead of one nested expression.

Multiple CTEs

A single WITH clause can define more than one CTE, separated by commas. Later CTEs can even reference earlier ones, letting you build up a complex query in clear, readable stages.

multiple_ctes.sql
WITH average_marks AS (
    SELECT AVG(marks) AS avg_marks FROM students
),
high_scorers AS (
    SELECT s.name, s.marks
    FROM students s, average_marks
    WHERE s.marks > average_marks.avg_marks
)
SELECT * FROM high_scorers ORDER BY marks DESC;
Result
+-------+-------+
| name  | marks |
+-------+-------+
| Priya | 91    |
| Alex  | 82    |
+-------+-------+

Recursive CTEs

MySQL also supports recursive CTEs, an advanced form that references itself to repeatedly build up a result — most commonly used for hierarchical data, like an organization chart where each employee has a manager who is also an employee.

recursive_preview.sql
-- A preview only — recursive CTEs are covered in more depth later
WITH RECURSIVE org_chart AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;
Not a Deep Dive Yet

You do not need to fully understand recursive CTEs right now. They are a specialized tool for hierarchical data (org charts, category trees, folder structures) and are introduced here only so the name is familiar when you encounter them later.

Common Mistakes

Avoid These Mistakes
  • Forgetting that a CTE only exists for the single query directly after it — it is not a permanent table.
  • Assuming a CTE is automatically faster than a subquery; in MySQL, it mainly improves readability rather than performance.
  • Reusing a CTE name that already matches a real table name, which can cause confusion.
  • Overcomplicating a simple query with a CTE when a plain WHERE clause would be clearer.

Best Practices

  • Reach for a CTE once a query has more than one or two levels of nested subqueries.
  • Give each CTE a clear, descriptive name that explains what it represents.
  • Break complex logic into multiple small, named CTEs rather than one giant one.
  • Use CTEs to make reports and analytics queries easier for teammates to review.

Frequently Asked Questions

Does a CTE improve query performance?

Not necessarily. In MySQL, a CTE mainly improves readability. The database may execute it similarly to an equivalent subquery under the hood.

Can I use a CTE more than once in the same query?

Yes. Once defined, a CTE can be referenced multiple times within the same main query, unlike a subquery which would need to be repeated in full.

Is a CTE the same as a VIEW?

No. A VIEW is saved permanently in the database and can be reused across many queries. A CTE exists only for the single query it is attached to.

When would I need a recursive CTE?

When working with hierarchical or tree-like data, such as an employee-manager chart, a category tree, or nested comment threads, where each row can reference another row in the same table.

Key Takeaways

  • A CTE is a named, temporary result set defined with WITH ... AS (...).
  • A CTE exists only for the duration of the query directly after it.
  • CTEs make complex, deeply-nested subqueries much easier to read.
  • A single WITH clause can define multiple CTEs, and later ones can reference earlier ones.
  • Recursive CTEs are an advanced tool for hierarchical data, like org charts.

Summary

CTEs give you a way to name a temporary result set and reference it clearly in your main query, replacing hard-to-read nested subqueries with clean, sequential steps.

In this lesson, you learned CTE syntax, rewrote a subquery from the previous lesson as a CTE, and got a brief introduction to recursive CTEs. Next, you will learn about window functions — a powerful tool for calculations across groups of rows.

Lesson 65 Completed
  • You understand what a CTE is and how WITH works.
  • You can rewrite a subquery as a CTE.
  • You can define multiple CTEs in one query.
  • You have a high-level sense of what recursive CTEs are used for.
  • You are ready to learn about window functions.
Next Lesson →

Window Functions