Recap of Common Table Expressions (CTE)

Hierarchical and Recursive Queries in SQL Server

Jasmin Ludolf

Content Developer

Outline of the course

What is recursion and how to use it?
How could you use Common Expression Tables (CTE) for hierarchical and recursive queries?
How could you represent hierarchy with SQL and how to query it?
Real-world examples (e.g. company organizations, bill of materials, flight plan)

Image of Russian Dolls and their recursive property.

What is a CTE?

Definition of a CTE

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single statement

Structure of a CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Use of CTEs

Manage complicated queries
Can be used within SELECT, INSERT, UPDATE, or DELETE statements
Several CTEs can be defined in one WITH statement
Combine several CTEs with UNION or JOIN
Substitute for a view
Self-reference a table
Recursion query

Define a CTE for an IT-organization

Select managers using a CTE:

WITH JOBS (id, SurName, JobTitle) AS

(
    SELECT 
          ID, 
          Name,
          Position
    FROM employee
    WHERE Position like '%Manager%'
),

WITH AS to define the CTE
SELECT on the Employee table

Define a CTE for an IT-organization

Select employees with salary over 10,000

SALARIES (ID,Salary) AS (
   SELECT
        ID,
        Salary
   FROM ITSalary
   WHERE Salary > 10000)

Define a CTE for an IT-organization

The whole query:

WITH JOBS (id, SurName, JobTitle) AS 
(    SELECT ID, Name, Position
        FROM employee 
        WHERE Position like '%Manager%'),

SALARIES (ID,Salary) AS
(    SELECT ID, Salary
        FROM ITSalary 
        WHERE Salary > 10000)

SELECT JOBS.Name, JOBS.Position, SALARIES.Salary
    FROM JOBS
    INNER JOIN SALARIES
    on JOBS.ID = SALARIES.ID;

SurNmames     | JobTitle          | Salary
Paul Smith    | IT Manager        | 15,000
Adam Peterson | Sourcing Manager  | 12,500
Anna Nilson   | Portfolio Manager | 10,500

Let's practice!

Hierarchical and Recursive Queries in SQL Server