SQL - CTE - Basics - CodeTributors

What you can expect from this content

Brief explanation of what a CTE is and how to use them to optimize your queries.

Short Description

A CTE or common table expression is a neater presentation on how to write a subquery (not to be confused with an Inline query). This definition specifies a named result set that can be used in the scope of a single SELECT, INSERT, DELETE or MERGE statement. What this basically means is that when you specify a CTE in a query you can only reference it once in a query. If you want to reference the data again you have to restate it or store the results in a table.

How to write a CTE

;with Name_Of_1st_CTE_Here as
(
SELECT STATEMENT GOES HERE
)
,Name_Of_2nd_CTE_Here as
(
SELECT STATEMENT GOES HERE
)
SELECT
*
FROM
Name_Of_1st_CTE_Here t1
INNER JOIN
Name_Of_2nd_CTE_Here t2 on t1.Id = t2.Id

Common Pitfalls of CTE’s

Column names in a CTE must be unique.
To debug multiline CTE issues. Start with the first CTE and incrementally add the other CTE’s until you find the issue.
Try to avoid using all the columns and only state the columns that you need.
When using CTE’s move the filters that you can from the join statements into the CTE to improve performance.
Use descriptive names for CTE so you can distinguish between them.
Don’t use CTE’s for small scale data queries

Example using AdevntureWorksDW2019

Normal Join

SELECT 
     fsr.EnglishProductSubcategoryName
    ,dc.FirstName
    ,dc.LastName
    ,dd.SpanishMonthName
FROM 
    AdventureWorksDW2019.dbo.FactSurveyResponse fsr
INNER JOIN
    AdventureWorksDW2019.dbo.DimCustomer dc on  fsr.EnglishProductCategoryName = 'Clothing'
                                            AND fsr.CustomerKey = dc.CustomerKey
                                            AND dc.MiddleName IS NULL
INNER JOIN
     AdventureWorksDW2019.dbo.DimDate dd on dd.DateKey = fsr.DateKey
                                        AND dd.FullDateAlternateKey = '2012-02-29'

Normal Join – Execution Plan

CTE Join

with cte_FactSurveyResponse as
(
    SELECT
         DateKey
        ,CustomerKey
        ,EnglishProductSubcategoryName
    FROM
        AdventureWorksDW2019.dbo.FactSurveyResponse fsr
    WHERE
        fsr.EnglishProductCategoryName = 'Clothing'
)
,cte_DimCustomer AS
(
    SELECT
         CustomerKey
        ,dc.FirstName
        ,dc.LastName
    FROM
        AdventureWorksDW2019.dbo.DimCustomer dc
    WHERE
        dc.MiddleName IS NULL
)
,cte_DimDate AS
(
    SELECT
         DateKey
        ,SpanishMonthName
    FROM
        AdventureWorksDW2019.dbo.DimDate dd
    WHERE
        dd.FullDateAlternateKey = '2012-02-29'
)
SELECT 
     fsr.EnglishProductSubcategoryName
    ,dc.FirstName
    ,dc.LastName
    ,dd.SpanishMonthName
FROM 
    cte_FactSurveyResponse fsr
INNER JOIN
    cte_DimCustomer dc on fsr.CustomerKey = dc.CustomerKey
INNER JOIN
     cte_DimDate dd on dd.DateKey = fsr.DateKey

CTE – Execution Plan

Pro’s and Con’s

Pros

Improves readability for large complex queries
Improves performance for large data queries
You can more easily manage your filters for the tables that you are querying on
You can more easily review the data that is being joined without executing another select statement

Cons

It can be troublesome to debug large scale queries especially if you can’t find the error in the code
Can sometimes perform worse than a normal join query

SQL – CTE – Basics