CTE and WITH Clause in MySQL

What a Common Table Expression Is

A CTE gives a name to a query result for the duration of one statement. It turns a complex SQL statement into readable stages without creating a permanent database object.

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

Verified Advanced Score Lab

CREATE TABLE advanced_scores (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(60) NOT NULL,
  class_name VARCHAR(20) NOT NULL,
  marks DECIMAL(5,2) NOT NULL
);

INSERT INTO advanced_scores VALUES
(1, 'Aarav',  'X-A', 86),
(2, 'Meera',  'X-A', 92),
(3, 'Sana',   'X-A', 92),
(4, 'Kabir',  'X-B', 74),
(5, 'Vihaan', 'X-B', 88),
(6, 'Riya',   'X-B', 81);

X-A has three students, total 270 and average 90. X-B has three students, total 243 and average 81. These checkpoints verify all four Day 8 lessons.

First CTE: Class Summary

WITH class_summary AS (
  SELECT class_name,
         COUNT(*) AS student_count,
         SUM(marks) AS total_marks,
         ROUND(AVG(marks), 2) AS average_marks
  FROM advanced_scores
  GROUP BY class_name
)
SELECT class_name, student_count,
       total_marks, average_marks
FROM class_summary
WHERE average_marks >= 85
ORDER BY class_name;

The CTE produces two summary rows. The outer query retains X-A because its average meets 85; X-B at 81 does not. A CTE does not itself guarantee display order, so ORDER BY belongs in the final result when presentation order matters.

Multiple CTEs as a Query Pipeline

WITH
class_summary AS (
  SELECT class_name, AVG(marks) AS class_average
  FROM advanced_scores
  GROUP BY class_name
),
above_class_average AS (
  SELECT s.student_id, s.student_name,
         s.class_name, s.marks,
         cs.class_average
  FROM advanced_scores AS s
  JOIN class_summary AS cs
    ON cs.class_name = s.class_name
  WHERE s.marks > cs.class_average
)
SELECT student_name, class_name, marks,
       ROUND(class_average, 2) AS class_average
FROM above_class_average
ORDER BY class_name, student_id;

The second CTE can refer to the first because class_summary appears earlier. Use one WITH clause and separate CTE definitions with commas.

Reuse a CTE and Use WITH with DML

A CTE can be referenced several times in the containing statement. This query compares the strongest and weakest class averages without repeating the aggregation:

WITH class_summary AS (
  SELECT class_name, AVG(marks) AS average_marks
  FROM advanced_scores
  GROUP BY class_name
)
SELECT hi.class_name AS highest_class,
       lo.class_name AS lowest_class
FROM class_summary AS hi
CROSS JOIN class_summary AS lo
WHERE hi.average_marks = (
        SELECT MAX(average_marks) FROM class_summary
      )
  AND lo.average_marks = (
        SELECT MIN(average_marks) FROM class_summary
      );

MySQL also permits WITH at supported SELECT, UPDATE and DELETE statement positions. Preview the CTE-driven row set with SELECT before executing data-changing DML, and use a transaction where appropriate.

CTE, Derived Table, View or Temporary Table?

Choose by scope, reuse, permissions and performance—not by the belief that one construct is universally superior.

Scope, Column Names and Dependencies

• CTE names must be unique within one WITH clause.
• A CTE may refer to earlier CTEs, not later ones at the same level.
• Explicit column-name lists must match the result column count.
• Without an explicit list, names come from the CTE query's select list.
• Do not reuse a base-table name as a CTE name unless deliberate shadowing is unmistakable.

WITH class_summary
     (class_name, student_count, average_marks) AS (
  SELECT class_name, COUNT(*), AVG(marks)
  FROM advanced_scores
  GROUP BY class_name
)
SELECT * FROM class_summary;

Optimizer Behavior and Practice

A nonrecursive CTE may be merged into the outer query or materialized. If materialized and referenced multiple times, MySQL materializes it once for the query and may add useful internal indexes. Recursive CTEs are materialized.

1. Validate each CTE body independently.
2. Check row grain and counts at every stage.
3. Filter early only when semantics remain identical.
4. Use EXPLAIN; CTE syntax alone does not promise speed.
5. Measure representative data and watch wide intermediate results.

Practice: create a CTE for class maximums; chain it to return top scorers; reference one summary CTE twice; rewrite the basic example as a derived table and compare plans.

Official References

• MySQL 8.4: WITH and Common Table Expressions
• MySQL 8.4: CTE Merge and Materialization
• MySQL 8.4: EXPLAIN Statement

Syntax and behavior were checked against the official MySQL 8.4 manual. Verify plans and limits on your own server before production use.