CTE dalam SQL: Panduan Lengkap dengan Contoh

Cara Membuat SQL CTE

Saat membuat CTE, kita menggunakan kata kunci WITH untuk memulai definisi CTE. Sintaks umum CTE adalah sebagai berikut:

WITH cte_name (column1, column2, ...) AS (
    -- Query that defines the CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query
SELECT ...
FROM cte_name;

Di mana:

• WITH: Memulai definisi CTE.
• cte_name: Nama yang diberikan untuk CTE (digunakan untuk mereferensikannya nanti).
• Daftar kolom opsional: Menentukan nama kolom untuk himpunan hasil CTE.
• Kueri utama: Mereferensikan CTE berdasarkan nama, memperlakukannya seperti tabel biasa.

Mari kita lihat contoh. Misalkan kita memiliki tabel Employees dan ingin memilih karyawan yang mendapatkan gaji di atas $50.000.

Langkah 1: Tulis kueri dasar

Kita mulai dengan menulis kueri dasar SELECT:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Langkah 2: Bungkus kueri menggunakan kata kunci WITH untuk membuat CTE

Gunakan kata kunci WITH untuk memberi nama pada CTE.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

Langkah 3: Gunakan CTE dalam kueri utama

Terakhir, rujuk CTE dalam pernyataan SELECT dengan memanggil nama CTE yang didefinisikan di atas.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Untuk merangkum langkah-langkah di atas, kita menggunakan kata kunci WITH untuk mendefinisikan CTE bernama HighEarningEmployees. Kueri di dalamnya digunakan untuk menghasilkan dataset sementara. Kueri utama mereferensikan HighEarningEmployees untuk menampilkan kolom yang ditentukan EmployeeID, FirstName, dan LastName.

Mengapa SQL CTE Bermanfaat

Dari contoh di atas, Anda mungkin bertanya-tanya mengapa kita menggunakan CTE ketika kueri sederhana pun memberikan hasil yang sama. Alasan-alasan berikut menjelaskannya:

Menyederhanakan kueri kompleks

CTE memecah pernyataan SQL yang kompleks menjadi bagian-bagian yang lebih kecil dan mudah dikelola, sehingga kode lebih mudah dibaca, ditulis, dan dirawat. 

Misalkan kita memiliki tiga tabel: Orders, Customers, dan Products. Kita ingin mencari total pendapatan yang dihasilkan oleh setiap pelanggan yang berbelanja pada 2024. Ketika kita menulis kueri tanpa menggunakan CTE, hasilnya tampak berantakan dan sulit dibaca serta dipahami.

-- Standard SQL: Hard to read nested logic
SELECT 
    c.CustomerName, 
    SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
JOIN Customers c 
    ON o.CustomerID = c.CustomerID
JOIN Products p 
    ON o.ProductID = p.ProductID
WHERE EXTRACT(YEAR FROM o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

Dengan menggunakan CTE, kita dapat memisahkan logika ke dalam format yang lebih mudah dibaca. Kita mengisolasi langkah "penyaringan dan penggabungan" terlebih dahulu, lalu melakukan agregasi.

-- Standard SQL: Cleaner with CTE
WITH OrderDetails AS (
    SELECT 
        o.OrderID, 
        c.CustomerName, 
        p.Price, 
        o.Quantity, 
        o.OrderDate
    FROM Orders o
    JOIN Customers c 
        ON o.CustomerID = c.CustomerID
    JOIN Products p 
        ON o.ProductID = p.ProductID
    WHERE EXTRACT(YEAR FROM o.OrderDate) = 2024
)
-- Main query
SELECT 
    CustomerName, 
    SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Dapat digunakan kembali

CTE membantu menghindari duplikasi dengan memungkinkan himpunan hasil yang sama digunakan kembali. Jika Anda perlu menghitung agregat (seperti jumlah) dan kemudian memfilter berdasarkan agregat tersebut, CTE sangat cocok.

Misalkan kita perlu menghitung rata-rata dan total penjualan untuk setiap kategori produk. Kita mendefinisikan perhitungannya sekali dalam CTE:

WITH CategorySales AS (
    SELECT 
        Category, 
        SUM(SalesAmount) AS TotalSales, 
        AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Select from the CTE where the pre-calculated TotalSales is high
SELECT 
    Category, 
    TotalSales, 
    AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Aplikasi lainnya

Selain menyederhanakan kueri dan kemampuan penggunaan ulang kode, CTE juga memiliki kegunaan lain. Saya tidak dapat membahas setiap kemungkinan penggunaan CTE secara mendetail. Kursus Data Manipulation in SQL kami adalah pilihan yang bagus jika Anda ingin terus berlatih. Namun, saya akan mendokumentasikan beberapa alasan utama lainnya di sini:

• Organisasi dan Keterbacaan Kueri: CTE meningkatkan keterbacaan kode SQL dengan membagi kueri menjadi langkah-langkah logis dan berurutan. Setiap langkah dalam proses kueri dapat direpresentasikan oleh CTE-nya sendiri, sehingga keseluruhan kueri lebih mudah diikuti.
• Penelusuran Data Hierarkis: CTE dapat membantu menavigasi hubungan hierarkis, seperti struktur organisasi, hubungan induk-anak, atau model data apa pun yang melibatkan tingkat bersarang. CTE rekursif berguna untuk mengkueri data hierarkis karena memungkinkan Anda menelusuri tingkat secara iteratif.
• Agregasi Multi-Level: CTE dapat membantu melakukan agregasi pada beberapa tingkat, seperti menghitung angka penjualan pada granularitas berbeda (mis., per bulan, triwulan, dan tahun). Menggunakan CTE untuk memisahkan langkah-langkah agregasi ini memastikan setiap tingkat dihitung secara mandiri dan logis.
• Menggabungkan Data dari Beberapa Tabel: Beberapa CTE dapat digunakan untuk menggabungkan data dari tabel berbeda, sehingga langkah penggabungan akhir menjadi lebih terstruktur. Pendekatan ini menyederhanakan join yang kompleks dan memastikan data sumber diatur secara logis untuk meningkatkan keterbacaan.

Teknik Lanjutan SQL CTE

CTE mendukung teknik SQL tingkat lanjut, menjadikannya serbaguna dan berguna untuk berbagai kasus penggunaan. Berikut adalah beberapa penerapan lanjutan CTE.

Banyak CTE dalam satu kueri

Anda dapat mendefinisikan beberapa CTE dalam satu kueri, yang memungkinkan transformasi dan perhitungan yang kompleks. Metode ini berguna ketika suatu masalah memerlukan beberapa tahap pemrosesan data, di mana setiap CTE mewakili satu tahap yang berbeda.

Misalkan kita memiliki data penjualan dalam tabel bernama Sales dan kita ingin menghitung total penjualan untuk setiap produk, mengidentifikasi produk dengan total penjualan di atas rata-rata, dan memberi peringkat pada produk-produk ini berdasarkan total penjualannya.

WITH ProductSales AS (
    -- Step 1: Calculate total sales for each product
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
), 
AverageSales AS (
    -- Step 2: Calculate the average of those totals
    -- Note: We can reference the previous CTE (ProductSales) here
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
), 
HighSalesProducts AS (
    -- Step 3: Filter products above the average
    SELECT ps.ProductID, ps.TotalSales
    FROM ProductSales ps
    CROSS JOIN AverageSales av
    WHERE ps.TotalSales > av.AverageTotalSales
)
-- Step 4: Rank the results
SELECT 
    ProductID, 
    TotalSales, 
    RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

Pada contoh di atas:

• CTE pertama (ProductSales) menghitung total penjualan per produk.
• CTE kedua (AverageSales) menghitung rata-rata total penjualan di seluruh produk.
• CTE ketiga (HighSalesProducts) memfilter produk yang total penjualannya melebihi rata-rata.
• Kueri terakhir memberi peringkat produk-produk ini berdasarkan total penjualannya.

CTE dalam Pernyataan UPDATE, DELETE, dan MERGE

Saat digabungkan ke dalam operasi UPDATE, DELETE, dan MERGE, CTE dapat menyederhanakan tugas manipulasi data, terutama saat berurusan dengan filter kompleks atau data hierarkis.

Menggunakan CTE dengan pernyataan UPDATE

Misalkan kita memiliki tabel Employees dengan kolom EmployeeSalary. Kita ingin memberikan kenaikan 10% kepada semua karyawan yang telah bekerja di perusahaan selama lebih dari 5 tahun.

-- Define a CTE to find employees hired more than 5 years ago
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    -- Standard SQL: Compare HireDate to 5 years before today
    WHERE HireDate <= CURRENT_DATE - INTERVAL '5' YEAR
)
-- Update salaries by 10% for long-term employees identified in the CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.10
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

CTE LongTermEmployees mengidentifikasi karyawan yang telah bekerja lebih dari lima tahun. Pernyataan UPDATE menggunakan CTE ini untuk menaikkan gaji secara selektif.

Menggunakan CTE dengan pernyataan DELETE

Sekarang, misalkan kita memiliki tabel bernama Products dan ingin menghapus semua produk yang tidak terjual dalam 2 tahun terakhir. Kita dapat menggunakan CTE untuk memfilter produk:

-- Define a CTE to identify products not sold in the last 2 years
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Standard SQL: Filter for dates older than 2 years ago
    WHERE LastSoldDate < CURRENT_DATE - INTERVAL '2' YEAR
)
-- Delete products identified as old from the main table
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

CTE OldProducts mengidentifikasi produk yang tidak terjual dalam dua tahun terakhir, lalu pernyataan DELETE menggunakan CTE ini untuk menghapus produk-produk tersebut.

Menggunakan CTE dengan pernyataan MERGE

Pernyataan MERGE dalam SQL memungkinkan pembaruan, penyisipan, atau penghapusan bersyarat dalam tabel target berdasarkan data di tabel sumber. Pada contoh berikut, CTE MergedInventory menggabungkan data inventaris baru dan yang sudah ada. Pernyataan MERGE lalu memperbarui kuantitas untuk produk yang sudah ada atau menyisipkan produk baru berdasarkan data CTE.

-- CTE to prepare the source data for the merge
WITH MergedInventory AS (
    SELECT 
        ni.ProductID, 
        ni.Quantity AS NewQuantity
    FROM NewInventoryData ni
)
-- Merge the prepared data into the Inventory table
MERGE INTO Inventory AS target
USING MergedInventory AS source
    ON target.ProductID = source.ProductID
-- Update existing products with new quantities
WHEN MATCHED THEN
    UPDATE SET target.Quantity = source.NewQuantity
-- Insert new products if they don't exist in the inventory
WHEN NOT MATCHED THEN
    INSERT (ProductID, Quantity) 
    VALUES (source.ProductID, source.NewQuantity);

Common Table Expressions (CTE) Rekursif

CTE rekursif adalah jenis khusus CTE yang mereferensikan dirinya sendiri di dalam definisinya, memungkinkan kueri melakukan operasi berulang. Ini membuatnya ideal untuk bekerja dengan data hierarkis seperti bagan organisasi.

Pengantar CTE rekursif

CTE rekursif adalah jenis khusus CTE yang mereferensikan dirinya dalam definisinya, memungkinkan kueri melakukan operasi berulang. Ini membuatnya ideal untuk bekerja dengan data berstruktur hierarkis atau pohon, seperti bagan organisasi, struktur direktori, atau perakitan produk. CTE rekursif memproses data secara iteratif, mengembalikan hasil selangkah demi selangkah hingga anggota rekursif tidak mengembalikan baris baru (kondisi terminasi).

Anggota jangkar dan rekursif

CTE rekursif terdiri dari dua bagian utama:

• Anchor Member (Jangkar): Bagian yang mendefinisikan kueri dasar yang memulai rekursi.
• Recursive Member (Rekursif): Bagian yang mereferensikan CTE itu sendiri, memungkinkan dilakukannya operasi "rekursif".

Misalkan kita memiliki tabel Employees, di mana setiap baris berisi EmployeeID, EmployeeName, dan ManagerID. Jika kita ingin menemukan semua bawahan langsung dan tidak langsung untuk manajer tertentu, kita mulai dengan anggota jangkar yang mengidentifikasi manajer tingkat atas.

Catatan: Dalam Standard SQL (PostgreSQL, MySQL, SQLite), Anda harus menggunakan kata kunci RECURSIVE.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: select the top-level manager
    SELECT 
        EmployeeID, 
        EmployeeName, 
        ManagerID, 
        1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Starting with the top-level manager
    
    UNION ALL
    
    -- Recursive member: find employees who report to the current managers
    SELECT 
        e.EmployeeID, 
        e.EmployeeName, 
        e.ManagerID, 
        eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh 
        ON e.ManagerID = eh.EmployeeID
)
-- Select the final result from the CTE
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

Cara kerjanya:

1. Jangkar: Kueri menjalankan Anchor Member terlebih dahulu, menemukan karyawan dengan ID 1.
2. Rekursi: Recursive Member berjalan, mencari karyawan yang ManagerID-nya cocok dengan EmployeeID yang ditemukan pada langkah sebelumnya.
3. Loop: Proses ini berulang (Level 1 menemukan Level 2, Level 2 menemukan Level 3) hingga tidak ada karyawan baru yang ditemukan.

Potensi Masalah atau Keterbatasan CTE dalam SQL

Memahami fitur dan keterbatasan CTE penting untuk menulis kueri yang logis dan mudah dibaca. Mari kita lihat beberapa keterbatasan dan potensi masalah penggunaan CTE di berbagai database.

Keterbatasan SQL Server dan Azure

Ada beberapa keterbatasan spesifik lingkungan untuk CTE SQL saat bekerja dengan SQL Server atau Azure Synapse Analytics. Di antaranya:

• SQL Server: Tingkat rekursi maksimum default untuk CTE rekursif adalah 100. Jika batas ini terlampaui tanpa penyesuaian, akan terjadi galat. Definisi CTE tidak dapat disarang langsung di dalam definisi CTE lain (meskipun Anda dapat merangkaikan beberapa CTE secara berurutan).
• Azure Synapse Analytics: Dukungan bervariasi menurut jenis pool spesifik. CTE rekursif saat ini tidak didukung di Dedicated SQL Pools (sebelumnya SQL DW). Namun, CTE rekursif didukung di Serverless SQL Pools. Selain itu, beberapa operasi DML (seperti UPDATE atau DELETE dengan CTE) mungkin memiliki pembatasan sintaks dibandingkan SQL Server standar.

Jika Anda bekerja dengan SQL Server, ketahuilah bahwa DataCamp memiliki banyak sumber daya hebat untuk membantu. Untuk memulai, saya sarankan mengambil kursus Introduction to SQL Server dari DataCamp untuk menguasai dasar-dasar SQL Server untuk analisis data. Anda dapat mencoba jalur karier SQL Server Developer kami, yang mencakup segala hal mulai dari transaksi dan penanganan galat hingga analisis deret waktu. Kursus Hierarchical and Recursive Queries in SQL Server kami langsung ke inti cara menulis kueri lanjutan di SQL Server, termasuk metode yang melibatkan CTE.

Potensi masalah lainnya

Meskipun CTE berguna untuk menyederhanakan kueri kompleks, ada beberapa jebakan umum yang perlu Anda ketahui. Di antaranya:

• Loop Tak Berujung dalam CTE Rekursif: Jika kondisi terminasi untuk CTE rekursif tidak terpenuhi, hal ini dapat mengakibatkan loop tak berujung, menyebabkan kueri berjalan tanpa henti. Untuk menghindari CTE rekursif berjalan tanpa henti, gunakan hint OPTION (MAXRECURSION N) untuk membatasi jumlah iterasi rekursif maksimum, di mana N adalah batas yang ditentukan.

  • Cara memperbaiki: Di SQL Server, gunakan hint OPTION (MAXRECURSION N) untuk membatasi jumlah iterasi rekursif maksimum. Di PostgreSQL, Anda dapat menggunakan klausa CYCLE untuk mendeteksi loop secara otomatis.

• Pertimbangan Kinerja: CTE rekursif dapat menjadi intensif sumber daya jika kedalaman rekursi tinggi atau dataset besar sedang diproses. Untuk mengoptimalkan kinerja, batasi data yang diproses di setiap iterasi dan pastikan penyaringan yang sesuai untuk menghindari tingkat rekursi yang berlebihan.

Kinerja: CTE vs Subquery

Mitos umum adalah bahwa CTE secara bawaan lebih cepat daripada subquery. Pada kenyataannya, sebagian besar pengoptimal kueri modern (seperti di SQL Server dan PostgreSQL) "meng-inline" CTE standar, artinya CTE diproses persis seperti subquery tanpa perbedaan kinerja.

Namun, CTE dapat memberikan dorongan kinerja melalui materialisasi, di mana database menghitung hasil CTE sekali dan menyimpannya untuk referensi berulang dalam kueri utama.

Saya menyarankan menggunakan CTE terutama untuk keterbacaan. Peningkatan kinerja bersifat situasional dan bergantung pada bagaimana database spesifik Anda menangani caching.

Kapan Menggunakan CTE vs. Teknik Lain

Meskipun CTE tepat untuk menyederhanakan kueri yang melibatkan tugas berulang, derived table, view, dan tabel sementara juga memiliki tujuan serupa. Tabel berikut menyoroti kelebihan dan kekurangan masing-masing metode serta kapan menggunakannya.

Kesimpulan

Menguasai CTE membutuhkan latihan, seperti hal lainnya: saya merekomendasikan mencoba jalur karier Associate Data Analyst in SQL dari DataCamp untuk menjadi analis data yang andal. Kursus Reporting in SQL juga akan membantu Anda mahir membangun laporan dan dasbor kompleks untuk penyajian data yang efektif. Terakhir, Anda sebaiknya memperoleh SQL Associate Certification untuk menunjukkan penguasaan Anda dalam menggunakan SQL untuk menyelesaikan masalah bisnis dan tampil menonjol di antara para profesional lainnya.