introduction to CTE Link to heading

A CTE (Common Table Expression) is temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement.

to give you a visual example

WITH CTE (param1, param2)
AS (
    some fancy query
)

previous to this post, I always called it ‘with block’, right now I am calling it common table expression.

CTE details Link to heading

let’s see an example

WITH employeeCTE (EmployeeNumber, Title)
AS
(
    SELECT NationalIDNumber, JobTitle
    FROM HumanResources.Employee
)

......

SELECT EmployeeNumber, Title FROM EmployeeCTE

  • let me restate one more time, the block before ellipsis is called CTE.

  • notice WITH statement, parameters inside brackets EmployeeNumber and Title correspond to columns returned from the inner query, called corresponding columns

  • CTE can be multiple, using comma to sperate

why CTE Link to heading

  • readability
  • substitute for a View
  • recurstion, we will talk about this later
  • easy to limit or perform GROUP BY
  • need CTE whenever we want to use ranking functions.

non-recursive CTE vs recursive CTE Link to heading

for non-recursive CTE we will just skip because we just use CTE as a result set and query out of it.

the interesting part is recursive CTE.

introduction to recursive CTE Link to heading

in general, recursive CTE is like following:

WITH expression_name (column_list)
AS
(
    -- Anchor member
    initial_query  
    UNION ALL
    -- Recursive member that references expression_name.
    recursive_query  
)
-- references expression name
SELECT *
FROM   expression_name

generally, this block has three parts:

  1. an initial query that returns base result set of CTE. it’s been called anchor member.
  2. a recursive query that references the CTE, therefore it’s been called recursive member. recursive members and anchor memeber have to be unioned by UNION ALL operator.
  3. a termination condition specified in recursive memeber that will terminate the execution of the recursive member.

execution order of recursive CTE Link to heading

  1. execute anchor member to form the base result set (R0), use this result for ’next iteration’.
  2. then execute the recusive member with the input result set from previous interation (Ri-1), until termination condition met.
  3. combine all result sets R0, R1, …, Rn using UNION ALL operator.

recursive CTE

recursive CTE and top-down recursion Link to heading

To me, recursive CTE is a top-down recursion. but wait, why is the structure different from normal top-down recursion?

let’s review a normal top-down recursion structure

private void recur(int param) {
    // base case
    if (param == certain number) return;

    // recursive part

    recur(param+1 or so..);
}

for normal top-down recursion, we will

first, explicitly state base case, which is the termination rule in recursive CTE btw, and then state recursive part.

well, recursive CTE contains all parts but in different order.

for recursive CTE

  • first state the most top case as a ‘seed’ set.
  • then state the recursive part
    • within the recursive part, termination rules hides in it.

so it all makes sense now.

miscallaneous Link to heading

  • top-down recursion: ’normal’ recursion
  • bottom-up recursion: dynamic programming

credits Link to heading