Apa Itu, Mengapa Penting, dan Cara Menggunakannya

Common Table Expression (CTE), juga dikenal sebagai “kueri WITH, digunakan untuk memecah kueri kompleks menjadi bagian yang lebih sederhana dan lebih mudah dikelola, sehingga meningkatkan keterbacaan kode SQL. Ini sangat berguna saat menulis kueri yang berisi subkueri kompleks. Ini memberikan nama sementara ke subkueri kompleks sebelum menggunakannya dalam kueri yang lebih besar.
Ada dua jenis CTE: Normal dan Rekursif (CTE Rekursif tidak tercakup dalam konteks ini).

Membangun Ekspresi Tabel Umum

Kueri CTE ditulis menggunakan sintaks berikut:

WITH cte_name (column_name) AS (query)
SELECT * FROM CTE_NAME;

Misalkan sebuah tabel bernama `orang`;

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | [email protected]    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | [email protected]     | Female | United Kingdom  | Plymouth    |
| 3  | Ileana       | Guerin      | [email protected]         | Female | Bulgaria        | null        |
| 4  | Walden       | Milmo       | null                        | Male   | Russia          | Ford        |
| 5  | Quincy       | Bromont     | null                        | Male   | China           | Lexus       |
| 6  | Maria        | Iddon       | [email protected]     | Female | Philippines     | Land Rover  |
| 7  | Rog          | McArdell    | [email protected]  | Male   | Poland          | null        |

Kode sumber, termasuk kueri untuk membuat tabel ini, dapat ditemukan di GitHub.

https://github.com/TheDataIsaac/The-Data-Diaries-with-Isaac/blob/main/PostgreSQL/Common%20Table%20Expression/cte.sql

Kami ingin mencari orang yang memiliki alamat email. Kita dapat menggunakan CTE untuk melakukan ini sebagai berikut:

-- Create A CTE named 'got_email' to select rows where the email is non-null
-- and select specific columns from the 'got_email' cte
WITH got_email AS (SELECT * FROM person WHERE email IS NOT NULL)
SELECT id,first_name,last_name FROM got_email;

Baris pertama CTE mendefinisikan CTE yang disebut `got_email`. CTE memilih semua baris dari tabel `person` dengan kolom `email` yang bukan nol.
Baris kedua kueri memilih subset kolom dari `got_email` CTE.

Kueri ini akan mengembalikan hasil berikut:

| id | first_name   | last_name   |
|----|--------------|-------------|
| 1  | Vikki        | Balsillie   |
| 2  | Lorettalorna | Fetteplace  |
| 3  | Ileana       | Guerin      |
| 6  | Maria        | Iddon       |
| 7  | Rog          | McArdell    |

Ada kata kunci tertentu yang tidak bisa langsung digunakan dalam CTE. Kata kunci seperti INSERT, UPDATE, DELETE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET. CTE terutama digunakan untuk menanyakan dan memilih data.

Beberapa Ekspresi Tabel Umum

Beberapa Ekspresi Tabel Umum dapat dibuat menggunakan sintaks di bawah ini;

WITH
   cte_name1 (column_name) AS (query),
   cte_name2 (column_name) AS (query)
SELECT * FROM cte_name1
UNION ALL
SELECT * FROM cte_name2;

CTE pertama dipisahkan dari CTE kedua dengan operator koma dan kemudian digabungkan dengan pernyataan SELECT di luar definisi CTE.
Beberapa CTE dapat digunakan untuk operasi UNION, UNION ALL, JOIN, INTERSECT, atau EXCEPT.
Kita akan menggunakan tabel yang sama di atas untuk menjelaskan beberapa CTE. Pada tabel, ada dua kategori record (orang); orang yang memiliki alamat email dan mobil, orang yang memiliki alamat email atau mobil.

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | [email protected]    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | [email protected]     | Female | United Kingdom  | Plymouth    |
| 3  | Ileana       | Guerin      | [email protected]         | Female | Bulgaria        | null        |
| 4  | Walden       | Milmo       | null                        | Male   | Russia          | Ford        |
| 5  | Quincy       | Bromont     | null                        | Male   | China           | Lexus       |
| 6  | Maria        | Iddon       | [email protected]     | Female | Philippines     | Land Rover  |
| 7  | Rog          | McArdell    | [email protected]  | Male   | Poland          | null        |

Kali ini, kita ingin memfilter tabel agar hanya menampilkan data yang orangnya memiliki alamat email dan mobil.
Kita dapat melakukannya menggunakan beberapa CTE:

--Create two CTEs named 'no_email' and 'no_car'
-- filter rows from the 'person' table except those in 'no_email' and 'no_car' CTEs
WITH no_email AS (SELECT * FROM person WHERE email IS NULL),
no_car AS (SELECT * FROM person WHERE car_make IS NULL)
SELECT * FROM person EXCEPT
(SELECT * FROM no_email UNION ALL
SELECT * FROM no_car);

Baris pertama mendefinisikan CTE `no_email`, yang memilih semua catatan dari tabel `orang` di mana kolom `email` adalah nol. Dipisahkan oleh operator koma adalah CTE `no_car` kedua, yang memilih semua catatan dari tabel `person` dengan kolom `car_make` adalah null.
Setelah definisi CTE adalah `SELECT terluar ` kueri yang memfilter baris dari tabel `orang` tidak termasuk baris dalam CTE `no_email` dan `no_car`.
Tabel yang dihasilkan;

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | [email protected]    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | [email protected]     | Female | United Kingdom  | Plymouth    |
| 6  | Maria        | Iddon       | [email protected]     | Female | Philippines     | Land Rover  |

MEMBANDINGKAN CTE DAN TABEL SEMENTARA

Anda mungkin bertanya-tanya, ini kedengarannya seperti tabel sementara, mengapa tidak menggunakan tabel sementara saja?. Ada beberapa perbedaan utama antara CTE dan tabel Sementara.
1. CTE didefinisikan menggunakan klausa `WITH`.
Tabel sementara dibuat menggunakan pernyataan `CREATE Temporary TABLE`.
2 .CTE tidak disimpan sebagai objek fisik di database, artinya tidak disimpan di disk.
Tabel Sementara adalah tabel fisik yang ada sementara di server database. Ini menghabiskan ruang disk.
3. CTE adalah kumpulan hasil sementara yang hanya ada selama durasi satu kueri yang mereferensikannya.
Tabel Sementara ada selama durasi sesi.
5. CTE tidak menjamin peningkatan kinerja runtime. Namun, ini membantu mengatur kueri Anda.
Tabel Sementara dapat meningkatkan kinerja runtime, terutama ketika kita perlu membuat referensi ke data yang sama beberapa kali.
6. CTE sendiri tidak dapat memiliki indeks secara langsung. Namun, Anda bisa membuat indeks pada tabel yang direferensikan CTE.
Anda bisa membuat indeks pada kolom tabel sementara, seperti yang Anda lakukan pada tabel biasa.

KESIMPULAN

CTE dapat membuat kueri kompleks lebih mudah dibaca dengan memecahnya menjadi segmen yang lebih kecil dan diberi nama sehingga lebih mudah untuk memahami logika kueri. Ini juga mempromosikan organisasi kode yang lebih baik.
CTE memberikan solusi yang tidak melibatkan penyimpanan fisik di server database. Dengan memanfaatkan manfaat CTE, pengembang dapat menulis kode yang mudah dipahami dan dapat dioptimalkan dengan lebih baik bila diperlukan.

Terima kasih telah membaca sampai akhir. Harap pertimbangkan untuk mengikuti penulis dan publikasi ini. Kunjungi Stackademic untuk mengetahui lebih lanjut tentang bagaimana kami mendemokratisasi pendidikan pemrograman gratis di seluruh dunia.