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.
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!