The basics of Common Table Expressions (CTEs)
Another quick post off the back of a SQL Lunch a did a while ago. Explore it via SQLFiddle: http://sqlfiddle.com/#!6/ad7f5/7/0
What is a CTE?
A Common Table Expression (CTE) is essentially a function defining a relation instead of a table.
This function outputs a table (like all queries) that is only present within the session, but data isn’t stored in tempdb like with a temporary table.
Why CTE’s?
CTEs are designed primarily to allow recursion within SQL – like a loop but ideal at working with hierarchies. This is excellent for tables that are self-referential … think employees!
Additionally, they’re great for generating sequences… think times, dates, etc
What’s the syntax?
WITH [Tbl Name] ([col],[names],..) AS ( SQL )
Produce multiple CTEs by comma separating them
WITH [Tbl Name] ([col],[names],..) AS ( SQL ), [Tbl Name 2] ([col],[names],..) AS ( SQL )
Then use a standard DML statement after it!
Where can I learn more?
https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
http://msdn.microsoft.com/en-gb/library/ms175972.aspx
http://blogs.msdn.com/b/sqlcat/archive/2011/04/28/optimize-recursive-cte-query.aspx