Why should one use Common Table Expressions?

Question

Hello,

I just read this link http://msdn.microsoft.com/en-us/library/ms186243.aspx

Here is the code to create table and insert data :-

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
    EmployeeID smallint NOT NULL,
    FirstName nvarchar(30)  NOT NULL,
    LastName  nvarchar(40) NOT NULL,
    Title nvarchar(50) NOT NULL,
    DeptID smallint NOT NULL,
    ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'S�nchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

They have given this CTE :-

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
        0 AS Level
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO

and got this output :-

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer       0
1         273        Vice President of Sales       1
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

Well, i wrote a simple join query and got nearly the same result except the level number :-

Select  e.ManagerId,
        m.FirstName as  [Manager Name],
        e.EmployeeId,
        e.FirstName as [Employee Name],
        e.Title
from dbo.MyEmployees e 
     LEFT JOIN dbo.MyEmployees m 
        On e.ManagerId = m.EmployeeId;

Is the purpose of CTE to just print the level number? Can anybody give me some concrete example as to why would one need CTE?

Thanks in advance :)

Answer 1

What if you wanted to show the Manager level people with all of their subordinates? (For example, all "level 2" employees with anyone who is under them - either as a direct report or as a report to a direct report, or as a report to a direct report to a direct report, etc.)

This would be fairly simple to do with a CTE. Not so easy without that recursion.

CTEs can also make queries much more readable instead of having to include the same subquery multiple times within a single SQL statement. That duplicate maintenance can also be a problem when you change one subquery but forget to change another. It's also a performance gain in many places because SQL Server can evaluate and save the CTE results once.