Or in the case of your simple query, you may also create and use a view or a user defined table valued function, in addition to a CTE. SQL Server also supports table variables in T-SQL, some other dbs may as well.