Ringkasan Common Table Expressions (CTE)

Kueri Hierarkis dan Rekursif di SQL Server

Jasmin Ludolf

Content Developer

Garis besar kursus

Apa itu rekursi dan cara menggunakannya?
Bagaimana menggunakan Common Expression Tables (CTE) untuk kueri hierarkis dan rekursif?
Bagaimana merepresentasikan hierarki dengan SQL dan cara mengkuerinya?
Contoh nyata (mis. struktur organisasi, bill of materials, rencana penerbangan)

Gambar Matryoshka dan sifat rekursifnya.

Apa itu CTE?

Definisi CTE

Menentukan himpunan hasil bernama sementara, disebut common table expression (CTE). Ini diturunkan dari kueri sederhana dan didefinisikan dalam cakupan eksekusi satu pernyataan

Struktur CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Kegunaan CTE

Mengelola kueri rumit
Dapat digunakan dalam pernyataan SELECT, INSERT, UPDATE, atau DELETE
Beberapa CTE dapat didefinisikan dalam satu pernyataan WITH
Gabungkan beberapa CTE dengan UNION atau JOIN
Sebagai pengganti view
Self-reference tabel
Kueri rekursif

Definisikan CTE untuk organisasi TI

Pilih manajer dengan CTE:

WITH JOBS (id, SurName, JobTitle) AS

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

WITH AS untuk mendefinisikan CTE
SELECT pada tabel Employee

Definisikan CTE untuk organisasi TI

Pilih karyawan dengan gaji di atas 10.000

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

Definisikan CTE untuk organisasi TI

Keseluruhan kueri:

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

Ayo berlatih!

Kueri Hierarkis dan Rekursif di SQL Server