Common Table Expressions (CTE)


USE AdventureWorks2012GO
--Defining CTEWITH EmplAS(
    SELECT * FROM HumanResources.Employee)
--Using CTESELECT Empl.* FROM EmplGO

And you can use several CTE’s 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