CTE Query Performance
February 5, 2012 Leave a Comment
The following question came up when working on my CTE presentation for SQL Saturday.
Does a query that JOINs a CTE to itself execute the CTE query once or twice?
For instance:
;WITH deptCTE(id, department, parent)
AS (SELECT id,department,parent FROM Departments)
SELECT q1.department,q2.department
FROM deptCTE q1
INNER JOIN deptCTE q2 ON q1.id = q2.parent
WHERE q1.parent IS NULL;
In the above example the query inside of the CTE gets run twice, and has no performance improvement over using the same query twice as a subquery. But it does clean up the syntax and reduce errors by only having the query in one place.
