Foto oleh panumas nikhomkhai via Pexels

Common Table Expression, atau CTE, adalah cara menulis subquery sementara yang diberi nama dan bisa dipakai ulang di dalam satu statement SQL yang sama. CTE ditulis menggunakan klausa WITH di awal query, menjadikannya solusi yang jauh lebih rapi dibanding menumpuk subquery bersarang yang sulit dibaca.

Selain meningkatkan keterbacaan, CTE juga membuka kemampuan untuk menulis query rekursif, yaitu query yang memanggil dirinya sendiri untuk mengolah data berstruktur hierarkis seperti struktur organisasi atau kategori bertingkat. Kemampuan ini tidak tersedia pada subquery biasa tanpa trik tambahan yang rumit.

Artikel ini membahas cara menulis CTE sederhana, CTE bertingkat yang saling merujuk, serta CTE rekursif untuk data hierarkis, lengkap dengan contoh kapan masing-masing pola cocok dipakai dalam pekerjaan sehari-hari.

Menulis CTE Sederhana dengan WITH

CTE sederhana diikuti SELECT yang memakainya

WITH PenjualanPerKategori AS ( SELECT p.Category, SUM(od.Quantity * od.UnitPrice) AS Total FROM OrderDetails od JOIN Products p ON p.ProductID = od.ProductID GROUP BY p.Category ) SELECT * FROM PenjualanPerKategori WHERE Total > 3000000; -- Expected: kategori dengan total penjualan -- di atas Rp3.000.000, dibaca dari CTE bernama
CTE hanya berlaku untuk satu statement yang langsung mengikutinya dan tidak disimpan sebagai objek permanen di database seperti VIEW. Setiap kali query dijalankan, CTE akan dihitung ulang dari awal, sehingga ia tidak menyimpan hasil secara fisik melainkan hanya memberi nama sementara untuk hasil subquery tersebut.

Menggabungkan Beberapa CTE Sekaligus

Dua CTE dipisahkan koma, CTE kedua bisa memakai CTE pertama

WITH Pesanan2024 AS ( SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024 ), PelangganAktif AS ( SELECT DISTINCT CustomerID FROM Pesanan2024 ) SELECT c.CustomerName FROM Customers c JOIN PelangganAktif pa ON pa.CustomerID = c.CustomerID; -- Expected: nama pelanggan yang memiliki -- minimal satu pesanan sepanjang tahun 2024
SQL Server mengizinkan satu klausa WITH berisi beberapa CTE yang dipisahkan koma, dan CTE yang ditulis belakangan boleh merujuk ke CTE yang ditulis sebelumnya. Pola ini sangat membantu memecah logika kompleks menjadi langkah-langkah kecil yang masing-masing punya nama jelas, jauh lebih mudah dibaca dibanding subquery bersarang tiga atau empat tingkat.

CTE Rekursif untuk Data Hierarkis

CTE rekursif terdiri dari anchor member dan recursive member

WITH StrukturOrganisasi AS ( SELECT EmployeeID, ManagerID, Nama, 0 AS Level FROM Employees WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.ManagerID, e.Nama, so.Level + 1 FROM Employees e JOIN StrukturOrganisasi so ON e.ManagerID = so.EmployeeID ) SELECT * FROM StrukturOrganisasi ORDER BY Level; -- Expected: seluruh karyawan dengan kolom Level -- menunjukkan kedalaman posisi di struktur organisasi
CTE rekursif terdiri dari dua bagian yang digabung UNION ALL: anchor member yang menjadi titik awal (biasanya baris paling atas tanpa ManagerID), dan recursive member yang terus memanggil nama CTE itu sendiri untuk mengambil level berikutnya, berhenti secara otomatis ketika tidak ada lagi baris yang cocok pada iterasi berikutnya.

Membatasi Kedalaman Rekursi dengan MAXRECURSION

OPTION MAXRECURSION mencegah rekursi tak berujung

WITH StrukturOrganisasi AS ( SELECT EmployeeID, ManagerID, Nama, 0 AS Level FROM Employees WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.ManagerID, e.Nama, so.Level + 1 FROM Employees e JOIN StrukturOrganisasi so ON e.ManagerID = so.EmployeeID ) SELECT * FROM StrukturOrganisasi OPTION (MAXRECURSION 50); -- Expected: query berhenti dengan error terkendali -- jika kedalaman rekursi melebihi 50 level
Secara default, SQL Server membatasi CTE rekursif hingga 100 level untuk mencegah infinite loop akibat data yang salah, misalnya struktur organisasi yang secara tidak sengaja membentuk lingkaran. OPTION (MAXRECURSION n) memungkinkan batas ini disesuaikan, termasuk diset ke 0 untuk menghilangkan batas sama sekali jika memang dibutuhkan dan datanya sudah dipastikan bersih.

Recursive CTE untuk Data Hierarkis

Recursive CTE untuk menelusuri struktur organisasi bertingkat

-- Tabel Employees punya kolom ManagerID yang menunjuk ke EmployeeID atasan WITH OrgChart AS ( -- Anchor member: level paling atas (tidak punya manager) SELECT EmployeeID, FullName, ManagerID, 1 AS Level FROM Employees WHERE ManagerID IS NULL UNION ALL -- Recursive member: join ke anchor/hasil iterasi sebelumnya SELECT e.EmployeeID, e.FullName, e.ManagerID, oc.Level + 1 FROM Employees e INNER JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID ) SELECT EmployeeID, FullName, Level FROM OrgChart ORDER BY Level; -- Hasil: seluruh karyawan lengkap dengan level jenjang strukturalnya

Recursive CTE berbeda dari CTE biasa karena terdiri dari dua bagian yang digabungkan dengan UNION ALL: anchor member yang menjadi titik awal rekursi, dan recursive member yang mereferensikan nama CTE itu sendiri untuk mengambil baris berikutnya berdasarkan hasil iterasi sebelumnya. Pola ini sangat cocok untuk data yang secara alami berbentuk hierarki, seperti struktur organisasi, kategori produk bertingkat, atau bill of material.

SQL Server secara default membatasi rekursi hingga 100 iterasi untuk mencegah infinite loop akibat data yang salah bentuk, misalnya ManagerID yang membentuk siklus. Batas ini dapat diubah dengan opsi OPTION (MAXRECURSION n), di mana nilai 0 berarti tanpa batas, namun pengaturan ini sebaiknya dipakai hati-hati karena data yang benar-benar bersiklus akan membuat query berjalan tanpa henti hingga sumber daya server habis.

Dibandingkan pendekatan lama menggunakan cursor atau loop prosedural untuk menelusuri hierarki baris demi baris, recursive CTE diproses secara set-based oleh mesin database sehingga umumnya lebih efisien untuk kedalaman hierarki yang moderat, meskipun pada hierarki yang sangat dalam dan tabel yang sangat besar, performanya tetap perlu diuji karena setiap iterasi tetap melibatkan operasi join.

Memakai CTE untuk Menyederhanakan Agregasi Multi-Tahap

CTE bertingkat memecah agregasi kompleks menjadi tahap-tahap bernama

-- Tanpa CTE: subquery bersarang membuat logika sulit dibaca SELECT CustomerID, TotalBelanja, RataRataBelanja FROM ( SELECT CustomerID, SUM(TotalAmount) AS TotalBelanja, AVG(SUM(TotalAmount)) OVER () AS RataRataBelanja FROM Orders GROUP BY CustomerID ) AS Ringkasan WHERE TotalBelanja > RataRataBelanja; -- Dengan CTE: setiap tahap punya nama dan mudah ditelusuri WITH RingkasanPerCustomer AS ( SELECT CustomerID, SUM(TotalAmount) AS TotalBelanja FROM Orders GROUP BY CustomerID ), RataRataKeseluruhan AS ( SELECT AVG(TotalBelanja) AS RataRata FROM RingkasanPerCustomer ) SELECT r.CustomerID, r.TotalBelanja FROM RingkasanPerCustomer r, RataRataKeseluruhan a WHERE r.TotalBelanja > a.RataRata; -- Hasil: customer dengan total belanja di atas rata-rata seluruh customer

CTE tidak hanya dipakai untuk rekursi; penggunaan yang jauh lebih umum di lapangan adalah memecah query agregasi berlapis menjadi beberapa blok WITH yang berurutan, di mana setiap CTE bisa mereferensikan CTE sebelumnya dalam definisi yang sama. Pendekatan ini menggantikan subquery bersarang yang semakin dalam dan semakin sulit dibaca ketika logika bisnis membutuhkan beberapa tahap perhitungan sebelum hasil akhir didapat.

Setiap CTE dalam rangkaian WITH diberi nama yang mendeskripsikan isinya, misalnya RingkasanPerCustomer atau RataRataKeseluruhan, sehingga pembaca kode bisa mengikuti alur logika tahap demi tahap tanpa harus membongkar tanda kurung subquery yang bertumpuk. Dari sisi eksekusi, SQL Server tetap memperlakukan beberapa CTE berurutan ini sebagai satu pernyataan tunggal dan optimizer bebas menyusun ulang urutan operasi selama hasil akhirnya tetap benar.

Penting dipahami bahwa CTE non-rekursif bukanlah tabel sementara yang disimpan hasilnya; definisi CTE di-inline oleh optimizer ke dalam query utama setiap kali direferensikan, sehingga jika satu CTE dipanggil berkali-kali dalam query yang sama, logika di dalamnya berpotensi dievaluasi berulang. Untuk kasus hasil antara yang benar-benar perlu dipakai ulang tanpa evaluasi ganda, tabel sementara atau variabel tabel bisa menjadi pilihan yang lebih sesuai, terutama ketika hasil antara tersebut berukuran besar dan dipakai lebih dari satu kali pada bagian query yang berbeda.

Memakai CTE untuk Menghapus Baris Duplikat

DELETE langsung terhadap CTE yang berisi ROW_NUMBER untuk menandai duplikat

-- Menghapus baris duplikat dengan CTE + ROW_NUMBER, menyisakan satu baris per kombinasi WITH DataBernomor AS ( SELECT CustomerID, Email, ROW_NUMBER() OVER ( PARTITION BY CustomerID, Email ORDER BY CreatedDate DESC ) AS Urutan FROM CustomerEmails ) DELETE FROM DataBernomor WHERE Urutan > 1; -- Hasil: hanya baris dengan CreatedDate paling baru yang dipertahankan, -- baris duplikat lain untuk kombinasi CustomerID+Email yang sama dihapus

CTE yang membungkus ROW_NUMBER() OVER (PARTITION BY ...) adalah pola umum untuk menandai baris duplikat berdasarkan kombinasi kolom tertentu, di mana setiap grup duplikat diberi nomor urut mulai dari 1 sesuai kriteria ORDER BY yang dipilih, misalnya baris dengan tanggal paling baru mendapat nomor 1.

Operasi DELETE, UPDATE, atau SELECT dapat dijalankan langsung terhadap CTE tersebut karena CTE diperlakukan sebagai hasil set sementara dalam cakupan satu pernyataan, sehingga baris dengan Urutan lebih dari 1 bisa langsung dihapus tanpa perlu tabel sementara terpisah. Pendekatan ini jauh lebih ringkas dibanding menulis subquery GROUP BY manual untuk mencari dan menghapus duplikat satu per satu, dan pola yang sama bisa dipakai untuk sekadar menandai baris duplikat lewat SELECT tanpa langsung menghapusnya, misalnya untuk ditinjau terlebih dahulu sebelum data benar-benar dibuang.

Kesalahan Umum yang Sering Terjadi

Mengira CTE Disimpan Permanen Seperti VIEW

CTE hanya hidup selama satu statement dieksekusi dan tidak bisa dipanggil lagi dari query terpisah setelahnya, berbeda dengan VIEW yang tersimpan sebagai objek database permanen.

Lupa Anchor Member dan Recursive Member Harus Berjumlah Kolom Sama

CTE rekursif akan gagal jika jumlah dan tipe data kolom pada anchor member tidak sama persis dengan recursive member, sebuah aturan yang sering terlewat saat menambahkan kolom baru di salah satu bagian saja.

Membuat Rekursi Tanpa Kondisi Berhenti yang Jelas

Jika data memiliki referensi melingkar, misalnya karyawan A menjadi manager dari karyawan B yang juga menjadi manager dari karyawan A, CTE rekursif bisa berjalan hingga MAXRECURSION tercapai dan menghasilkan error, bukan hasil yang masuk akal.

Kapan Pendekatan Ini Tidak Cocok Dipakai

CTE paling cocok untuk query yang butuh dipecah menjadi langkah-langkah bernama demi keterbacaan, atau untuk kasus rekursif pada data hierarkis yang memang tidak punya alternatif lain yang lebih sederhana tanpa CTE.

Untuk logika yang akan dipakai berulang kali di banyak query berbeda, VIEW atau stored procedure lebih tepat karena bisa dipanggil tanpa menulis ulang definisinya, sementara CTE harus ditulis ulang setiap kali dibutuhkan karena sifatnya yang sementara.

Ringkasan

CTE memberikan cara menulis query kompleks yang jauh lebih mudah dibaca dibanding subquery bersarang, sekaligus membuka kemampuan rekursi untuk data hierarkis. Gunakan CTE sederhana untuk memecah logika panjang, dan CTE rekursif khusus untuk struktur data yang memang berbentuk pohon atau hierarki bertingkat.