Dalam SQL, klausa WITH juga dikenal sebagai CTE. Ini adalah fitur canggih yang memungkinkan kita membuat kumpulan hasil sementara dalam kueri. Salah satu peran utama CTE adalah menyederhanakan kueri kompleks menjadi subkueri yang lebih kecil dan dapat digunakan kembali. Hal ini membantu membuat kode lebih mudah dibaca dan dipelihara dalam jangka panjang.
Bergabunglah dengan kami dalam tutorial ini saat kami menjelajahi cara kerja Ekspresi Tabel Umum menggunakan klausa WITH dan fungsionalitas yang didukung.
Persyaratan:
Untuk tujuan demonstrasi, kami akan menggunakan yang berikut ini:
- MySQL versi 8.0 ke atas
- Basis data sampel Sakila
Setelah persyaratan yang diberikan terpenuhi, kita dapat melanjutkan untuk mempelajari lebih lanjut tentang CTE dan klausa WITH.
SQL DENGAN Klausul
Klausa WITH memungkinkan kita untuk mendefinisikan satu atau lebih kumpulan hasil sementara yang dikenal sebagai Ekspresi Tabel Umum.
Kita bisa mereferensikan CTE yang dihasilkan di kueri utama seperti tabel atau kumpulan hasil lainnya. Ini memainkan peran penting dalam membuat kueri SQL modular.
Meskipun sintaks CTE mungkin sedikit berbeda tergantung pada kebutuhan Anda, berikut ini menunjukkan sintaks dasar CTE dalam SQL:
DENGAN cte_name (kolom1, kolom2, ...) AS (
-- Permintaan CTE
PILIH ...
DARI ...
DI MANA ...
)
-- Permintaan utama
PILIH ...
DARI ...
GABUNG cte_name DI ...
DI MANA ...
Kita mulai dengan kata kunci WITH yang memberitahu database SQL yang ingin kita buat dan gunakan CTE.
Selanjutnya, kami menentukan nama untuk CTE yang memungkinkan kami untuk mereferensikannya di kueri lain.
Kami juga menentukan daftar nama kolom opsional jika CTE menyertakan alias kolom.
Selanjutnya, kita melanjutkan untuk mendefinisikan kueri CTE. Ini berisi semua tugas atau data yang dilakukan CTE yang diapit dalam tanda kurung.
Terakhir, kami menentukan kueri utama yang mereferensikan CTE.
Contoh Penggunaan:
Salah satu cara terbaik untuk memahami cara menggunakan dan bekerja dengan CTE adalah dengan melihat contoh praktis.
Ambil contoh database sampel Sakila. Misalkan kita ingin mencari 10 pelanggan teratas dengan jumlah persewaan terbanyak.
Lihatlah CTE yang ditunjukkan berikut ini.
Menggunakan Klausa SQL WITH untuk menemukan 10 pelanggan teratas dengan jumlah sewa tertinggi:
DENGAN CustomerRentals SEBAGAI (PILIH c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) SEBAGAI rental_count
DARI pelanggan c
GABUNG rental r ON c.customer_id = r.customer_id
KELOMPOK BERDASARkan c.customer_id, c.first_name, c.last_name
)
PILIH *
DARI Penyewaan Pelanggan
PESANAN BERDASARKAN rental_count DESC
BATAS 10;
Dalam contoh yang diberikan, kita mulai dengan mendefinisikan CTE baru menggunakan kata kunci WITH diikuti dengan nama yang ingin kita tetapkan ke CTE. Dalam hal ini, kami menyebutnya “Rental Pelanggan”.
Di dalam badan CTE, kami menghitung jumlah sewa untuk setiap pelanggan dengan menggabungkan tabel pelanggan dan persewaan.
Terakhir, di kueri utama, kita memilih semua kolom dari CTE, mengurutkan hasilnya berdasarkan jumlah sewa (urutan menurun), dan membatasi keluaran hanya ke 10 baris teratas.
Hal ini memungkinkan kami mengambil pelanggan dengan jumlah rental terbanyak seperti yang ditunjukkan pada output berikut:
CTE Rekursif
Dalam beberapa kasus lain, Anda mungkin berurusan dengan struktur data hierarki. Di sinilah CTE rekursif berperan.
Mari kita ambil contoh kasus di mana kita ingin menavigasi organisasi hierarkis atau merepresentasikan struktur mirip pohon. Kita dapat menggunakan kata kunci WITH RECURSIVE untuk membuat CTE rekursif.
Karena tidak ada data hierarki yang dapat kita gunakan dalam database Sakila untuk mendemonstrasikan CTE rekursif, mari kita buat contoh dasar.
BUAT TABEL departemen (department_id INT KUNCI UTAMA AUTO_INCREMENT,
nama_departemen VARCHAR(255) BUKAN NULL,
parent_department_id INT,
KUNCI ASING (parent_department_id) REFERENSI departemen (department_id)
);
MASUKKAN KE departemen (nama_departemen, id_departemen_induk)
NILAI
('Perusahaan', NULL),
('Keuangan', 1),
('HR', 1),
('Akuntansi', 2),
'Perekrutan', 3),
('Penggajian', 4);
Dalam hal ini, kami memiliki contoh tabel 'departemen' dengan beberapa data acak. Untuk mencari struktur hierarki departemen, kita dapat menggunakan CTE rekursif sebagai berikut:
DENGAN Hierarki Departemen REKURSIF SEBAGAI (PILIH id_departemen, nama_departemen, id_departemen_induk
DARI departemen
DIMANA parent_department_id ADALAH NULL
PERSATUAN SEMUA
PILIH d.department_id, d.department_name, d.parent_department_id
DARI departemen d
GABUNG DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
PILIH *
DARI Hirarki Departemen;
Dalam hal ini, CTE rekursif dimulai dengan departemen yang memiliki NULL “parent_department_id” (departemen akar) dan secara rekursif mengambil departemen anak.
Kesimpulan
Dalam tutorial ini, kita mempelajari tentang fitur paling mendasar dan berguna dalam database SQL seperti Common Table Expressions dengan memahami cara bekerja dengan kata kunci WITH.