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

Search