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.

database icon
From MySQL query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and MySQL