Lesson 20. Common Table Expressions (CTEs)

CTEs are virtual datasets that you can create. When you create your CTE, it can be treated just like a table.

The use case for CTEs is when you want to encapsulate business logic. Sometimes queries get too hairy and it is easier to just separate the datasets so the query is easier to write.

The name of the columns that define the output of your CTE do not have to match the columns of the SQL statement used to create the CTE. Sometimes it is nice that they are the same. Other times, it is nice to have them different, especially if you are running a calculation on a particular column that winds up redefining the column definition. A good example would be running SUM() on DollarAmount. That column now represents total dollar amount and now the original column name is not very informative.

CTE Syntax

;
WITH CTEName(Column1, Column2,...ColumnN)
AS(
--SQL statement that encapsulates complex business logic
)
--The main SQL statement that joins on the CTE

Examples

Basic CTE Example

USE AdventureWorks2016

;
WITH PersonWithNoMiddleName(BusinessEntityID, FirstName, LastName)
AS(
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE MiddleName IS NULL
)
SELECT 
pwnmn.FirstName,
pwnmn.LastName,
e.EmailAddress
FROM Person.EmailAddress e
JOIN PersonWithNoMiddleName pwnmn
ON pwnmn.BusinessEntityID = e.BusinessEntityID