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 = 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
Shared queries and folders ✅ Version history ✅ One-click connection to MySQL ✅
Get more done, together, with PopSQL and MySQL