Common Table Expressions (CTE)

25 Sep 2012 
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

Tags

  • SQL
  • CTE
  • T-SQL
  • TSQL
  • SQL Server
  • Common Table Expressions