Common Table Expression (CTE) adalah salah satu bentuk query SQL yang digunakan untuk menyederhanakan JOIN pada SQL kedalam subqueries dan mampu memberikan query yang bersifat hieararki. CTE dikenal dengan istilah Hierarchical and recursive queries in SQL. Dalam tulisan ini kita akan mempelajari apa itu CTE dan contoh dari CTE itu sendiri.
Mengenal CTE
CTE pertama kali diperkenalkan pada SQL Server 2005. CTE sendiri adalah temporary result set yang dapat digunakan sebagai referensi bagi statement SQL setelahnya. Walaupun sering disebut sebagai pengganti dari subquery namun sebenarnya CTE lebih lekat kepada views dan store procedures. Saat ini CTE dapat digunakan pada banyak DBMS seperti SQL Server (2005 above), Oracle (11g r2), PostgreSQL (8.4 above), MariaDB (10.2 above), MySQL (8.0 above), SQLite (3.8.3 above), dan DBMS lainnya.
Secara ringkas CTE didefinisikan dengan operator WITH. Anda dapat mendefinisikan satu atau lebih CTE dalam query anda. Contoh dari penggunaan CTE dengan satu definisi query.
Dari contoh di atas dapat dilihat bahwa CTE yang kita definisikan dapat kita gunakan pada query seperti layaknya menggunakan tabel baru.
Manfaat CTE
Beberapa manfaat dari penggunaan CTE dalam query anda antara lain:
- Readability - Penggunaan CTE memudahkan developer dalam membaca query kompleks yang telah dibuat. Dengan pola hieararki maka query akan lebih mudah dibaca dibandingkan kita membuat banyak subqueri atau membuat beberapa view terpisah.
- Recursion - CTE mendukung pembuatan recursive queries, dimana sebuah queri dapat memanggil dirinya sendiri. Sangat bermanfaat ketika kita perlu bekerja dengan data bersifat hierarchical seperti struktur organisasi.
- Ranking - kapanpun anda ingin menggunakan fungsi ranking maka anda dapat menggunakan ROW_NUMBER(), RANK(), NTILE(), dan sebagainya
Selain hal tersebut di atas ada banyak manfaat lain dari penggunaan CTE. Manfaat ini tentunya akan kita temukan pada tulisan berikutnya setelah kita menyelami lebih jauh contoh-contoh dari CTE.
Jenis-Jenis CTE
CTE sendiri dari sifat querinya dapat dibagi atas dua kategori. Masing-masing kategori tersebut akan kita bahas pada tulisan berikutnya dari seri CTE ini.
- Non-Recursive CTE - Seperti namanya, CTE ini tidak menggunakan recursion. CTE ini tidak mereferensikan diri sendiri layanya sebuah view yang sering kita buat dalam queri SQL.
- Recursive CTE - CTE yang mereferensikan dirinya sendiri dapat disebut sebagai Recursive CTE. Ini adalah salah satu keunggulan terbesar dari CTE. Sejalan dengan dieksekusinya queri, SQL akan mengulang set data yang dihasilkan CTE sampai pada suatu kondisi terpenuhi (tentunya kita tidak ingin membuat CTE berjalan tak berhingga).
Demikian Pendahuluan dalam pengenalan CTE. Semoga bermanfaat dan Happy Coding!
Sumber:
https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
https://www.essentialsql.com/introduction-common-table-expressions-ctes/