How to Write a Common Table Expression 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.

database icon
SQL editing that just rocks
PopSQL and SQL Server, better together