How to Write a Common Table Expression in MySQL

Common table expressions (CTEs) are a great way to break up complex queries. MySQL started supporting this in version 8. Here's a simple query to illustrate how to write a CTE:

with beta_users as (
  select *
  from users
  where beta is true
)
select events.*
from events
inner join beta_users on beta_users.id = events.user_id;

You can find more complex examples of using CTE's in How to Avoid Gaps in Data in MySQL and in Calculating Cumulative Sums in MySQL.

Cta

Get started with MySQL and PopSQL in minutes