MySQL + SQL · Lesson 77

CTE and WITH Clause in MySQL

What is a CTE?

A CTE (Common Table Expression) is a temporary named result defined with WITH, used to make complex queries readable. MySQL 8+.

Example

WITH high_scorers AS (
  SELECT name, marks FROM students WHERE marks >= 90
)
SELECT * FROM high_scorers ORDER BY marks DESC;

The CTE "high_scorers" is defined once and used like a table in the main query.

Why Use CTEs?

Break a big query into readable named steps.
Reuse the same subquery result multiple times.

Summary

A CTE is a named temporary result defined with WITH.
Makes complex queries cleaner and more readable.