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;  The following execution plan is produced showing that the Departments table is hit twice with a table scan each time with the same cost. …

CTE Query Performance Read more »