Using WITH Expressions in MySQL 8.0: Benefits, Examples, and Performance Comparison

Common table expressions (CTEs) have long been supported in commercial databases like Oracle and SQL Server, but MySQL only added the WITH feature in version 8.0. Two main advantages are highlighted: usability and efficiency.

Example 1 – Usability

A table t1 is created with several columns and indexes. After inserting 1,000 test rows, a traditional view is defined to retrieve aggregated data. The view requires repeated materialization, which can be costly.

Using a WITH expression, the same query is rewritten to compute the count and rank in a single statement, avoiding repeated view materialization. The query plan for the WITH version shows one less materialization step, resulting in faster execution.

Example 2 – Functional Use

MySQL historically could not reopen a temporary table multiple times, leading to errors such as ERROR 1137 (HY000): Can't reopen table: 'ytt_tmp1' . By defining a CTE that selects from the temporary table, the same logic can be expressed without reopening the table, eliminating the error.

Both examples demonstrate that WITH expressions simplify query writing, reduce resource consumption, and improve performance, especially when the same subquery is referenced multiple times.

Readers are encouraged to explore the MySQL manual for deeper details on CTE usage.