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

Produce multiple CTEs by comma separating them

 [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?