Common table expressions are as performant as inline table queries. This was something I have to prove to myself. I ran the same query written both as an inline table and CTE several times. for 629,000 rows I had the same execution time (13 seconds) every single time with both queries. The query plan was identical, down to the the estimate total subtree cost for each operation. The advantage for a CTE comes out in iterative processing and a cleaner style (in my opinion).
EXAMPLE 1
SELECTMIN(a.Date1) AS Date1,a.PersonIDFROM(SELECT DISTINCTDate1,PersonIDFROMTable2UNIONSELECT DISTINCTDate1,OtherPersonID AS PersonIDFROMTable1
) AS aLEFT JOIN Table3 (nolock) AS bON (ISNULL(a.PersonID,'?') = b.SomeKey SomeKey)
WHERE b.SomeKey IS NULL
GROUP a
EXAMPLE 2
WITHa AS(SELECT DISTINCTDate1,PersonIDFROMTable2UNION SELECT DISTINCT Date1,OtherPersonID AS PersonID FROMTable1
) SELECT MIN(a.Date1) AS Date1,a.PersonID
FROM a LEFT JOIN Table3 (nolock) AS b ON (ISNULL(a.PersonID,'?') = b.SomeKey )WHERE b.SomeKey IS NULL
GROUP a BY .PersonID BY .PersonID
EXAMPLE 1
SELECTMIN(a.Date1) AS Date1,a.PersonIDFROM(SELECT DISTINCTDate1,PersonIDFROMTable2UNIONSELECT DISTINCTDate1,OtherPersonID AS PersonIDFROMTable1
) AS aLEFT JOIN Table3 (nolock) AS bON (ISNULL(a.PersonID,'?') = b.SomeKey SomeKey)
WHERE b.SomeKey IS NULL
GROUP a
EXAMPLE 2
WITHa AS(SELECT DISTINCTDate1,PersonIDFROMTable2UNION SELECT DISTINCT Date1,OtherPersonID AS PersonID FROMTable1
) SELECT MIN(a.Date1) AS Date1,a.PersonID
FROM a LEFT JOIN Table3 (nolock) AS b ON (ISNULL(a.PersonID,'?') = b.SomeKey )WHERE b.SomeKey IS NULL
GROUP a BY .PersonID BY .PersonID
