SQL — Common Table Expressions (CTEs) - Simplify Complex Queries Using WITH

The teacher needs to first summarize class marks and then filter that summary. Naming the intermediate result makes the two stages easier to inspect.

A common table expression gives a query result a name within one statement. WITH can separate stages of a complex report without creating a permanent table.

Define the CTE query after WITH, then reference its name in the main query or a later permitted CTE. Its scope is that statement. The optimizer may merge or materialize it according to engine rules, so the name does not guarantee one stored computation reused at no cost.

Related concepts: Subqueries and Group and Summarize and Recursive Queries.

A CTE (Common Table Expression) is a temporary result set defined using the WITH clause that you can reference within a SELECT, INSERT, UPDATE, or DELETE query. It's like giving a name to a subquery and treating it like a table.

SQL · portable concepts; preserved examples identify their engine

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

SQL · portable concepts; preserved examples identify their engine

Consider the following marks table:

CREATE TABLE marks (
  roll_no INT,
  name VARCHAR(50),
  subject VARCHAR(30),
  marks INT
);

INSERT INTO marks VALUES
(1, 'Aarav', 'Maths', 95),
(2, 'Diya', 'Maths', 85),
(3, 'Sneha', 'Maths', 92),
(4, 'Karan', 'Maths', 78);

SQL · portable concepts; preserved examples identify their engine

WITH ranked_students AS (
  SELECT
    roll_no,
    name,
    marks,
    RANK() OVER (ORDER BY marks DESC) AS rank
  FROM marks
  WHERE subject = 'Maths'
)
SELECT * FROM ranked_students;

roll_no | name   | marks | rank
--------+--------+-------+------
1       | Aarav  | 95    | 1
3       | Sneha  | 92    | 2
2       | Diya   | 85    | 3
4       | Karan  | 78    | 4

• Readability: Replaces long nested subqueries
• Reusability: Can use the same CTE in multiple places in your main query
• Modularity: Makes code easier to debug and maintain

SQL · portable concepts; preserved examples identify their engine

WITH maths_marks AS (
  SELECT * FROM marks WHERE subject = 'Maths'
),
top_scorers AS (
  SELECT name, marks FROM maths_marks WHERE marks > 90
)
SELECT * FROM top_scorers;

name   | marks
--------+-------
Aarav  | 95
Sneha  | 92

SQL · portable concepts; preserved examples identify their engine

Let’s say we want to generate a sequence of roll numbers:

WITH RECURSIVE seq AS (
  SELECT 1 AS roll_no
  UNION ALL
  SELECT roll_no + 1 FROM seq WHERE roll_no < 5
)
SELECT * FROM seq;

roll_no
--------
1
2
3
4
5

• Generating class toppers using RANK()
• Creating filtered views like pass/fail lists
• Building roll number series using recursive CTEs
• 📚 Comparing subject-wise performance across terms

SQL · portable concepts; preserved examples identify their engine

• Use meaningful CTE names
• Keep logic within CTEs modular
• Avoid deeply nested subqueries; use chained CTEs instead
• Use recursive CTEs for tree-like or sequence generation

A CTE is not a stored view or a temporary table that automatically remains available for the next statement. It does not inherently make a query faster. Recursive CTEs need a separate stopping and cycle-handling design.

Name the class-average result, then select classes averaging at least 80.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

WITH class_average AS (SELECT class, AVG(marks) AS average_marks FROM practice_students GROUP BY class) SELECT class, average_marks FROM class_average WHERE average_marks >= 80 ORDER BY class;

The intermediate result has one average per class. The outer condition keeps 10A at 85 and excludes 9B at 70.

A common table expression gives a query result a name within one statement. WITH can separate stages of a complex report without creating a permanent table.

The intermediate result has one average per class. The outer condition keeps 10A at 85 and excludes 9B at 70.

Continue your understanding with Subqueries and Group and Summarize and Recursive Queries.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.