Herhaling van Common Table Expressions (CTE)

Hiërarchische en recursieve queries in SQL Server

Jasmin Ludolf

Content Developer

Cursusoverzicht

Wat is recursie en hoe gebruik je het?
Hoe kun je Common Table Expressions (CTE) gebruiken voor hiërarchische en recursieve queries?
Hoe modelleer je een hiërarchie in SQL en hoe query je die?
Voorbeelden uit de praktijk (bijv. organisaties, stuklijst, vluchtplan)

Afbeelding van Russische poppen en hun recursieve eigenschap.

Wat is een CTE?

Definitie van een CTE

Bepaalt een tijdelijke benoemde resultset, een common table expression (CTE). Afgeleid van een simpele query en gedefinieerd binnen de uitvoeringsscope van één statement

Structuur van een CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Gebruik van CTE’s

Beheer ingewikkelde queries
Te gebruiken binnen SELECT, INSERT, UPDATE of DELETE
Meerdere CTE’s in één WITH-statement
Combineer CTE’s met UNION of JOIN
Vervanging voor een view
Zelfverwijzing naar een tabel
Recursieve query

Definieer een CTE voor een IT-organisatie

Selecteer managers met een CTE:

WITH JOBS (id, SurName, JobTitle) AS

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

WITH AS om de CTE te definiëren
SELECT op de tabel Employee

Definieer een CTE voor een IT-organisatie

Selecteer medewerkers met salaris boven 10.000

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

Definieer een CTE voor een IT-organisatie

De volledige 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

Laten we oefenen!

Hiërarchische en recursieve queries in SQL Server