Common Table Expressions (CTE)

USE AdventureWorks2012
GO

--Defining CTE
WITH Empl AS(SELECT * FROM HumanResources.Employee)
--Using CTE
SELECT Empl.* FROM Empl
GO

Moreover, you can use several CTEs in one statement.

USE AdventureWorks2012 
GO

--Defining CTE
WITH Empl AS ( SELECT * FROM HumanResources.Employee ),
Pers AS ( SELECT * FROM Person.Person )
--Using CTE
SELECT Empl.*, Pers.*
FROM Empl INNER JOIN Pers
ON Empl.BusinessEntityID = Pers.BusinessEntityID
GO
· SQL, CTE, T-SQL, TSQL, SQL Server, Common Table Expressions