Common Table Expression (CTE)

Do you know what a Common Table Expression is? It is a temporary named result set that can make your SQL easier to read and maintain.

Where can I use it? Often one encounters a summary screen that has some basic information, along with a sum or count. One example is for instance details about an employee, plus a count of the number of people reporting to him. This can lead to some nasty SQL if you write it all in one statement. This is where CTE steps in. You can create a CTE for the number of people reporting to an employee, and join that with the employees table like it was a normal table.

In the example below the CTE is a result set with a ManagerID and the number of people reporting to that manager. It is then joined to the Employee table. Do you think it makes the SQL cleaner and easier to read?

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
 SELECT ManagerID, COUNT(*)
 FROM HumanResources.Employee AS e
 WHERE ManagerID IS NOT NULL
 GROUP BY ManagerID
)
SELECT e.EmployeeID, e.BirthDate, ISNULL(DirectReports, 0) as DirectReports
FROM DirReps as d right outer join HumanResources.Employee as e on d.ManagerID = e.EmployeeID
ORDER BY e.EmployeeID;
GO

You can find some additional info here.