Introducing PopSQL + dbt. The first SQL editor with built-in dbt support.Read more →

How to Write a Common Table Expression in SQL Server in SQL Server

Common table expressions (CTEs) are a great way to break up complex queries. SQL Server has supported this from the early beginning, starting with the 2005 version. Here's a simple query to illustrate how to write a CTE:

with free_users as (
  select *
  from users
  where plan = 'free'
select user_sessions.*
from user_sessions
inner join free_users on = user_sessions.user_id
order by;

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

Ready for a modern SQL editor?