O'Reilly logo
  • Dustin Waybright thinks this is interesting:

WITH C AS ( SELECT ... FROM T1 ) SELECT ... FROM C AS C1 INNER JOIN C AS C2 ON ...;

From

Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Note

Multiple instances of the CTE being aliased and referred to itself. Not possible with derived tables.