SQL - Ekspresi Tabel Umum (CTE)

Hai teman-teman, para ahli SQL masa depan! Hari ini, kita akan mengemban perjalanan yang menarik ke dunia Ekspresi Tabel Umum, atau CTE untuk pendek. Jangan khawatir jika Anda masih baru dalam programming – saya akan memandu Anda langkah demi langkah, seperti yang telah saya lakukan untuk ribuan murid selama tahun-tahun mengajar saya. Jadi, ambil minuman favorit Anda, duduk nyaman, dan mari kita masuk ke dalam!

Ekspresi Tabel Umum di SQL

Bayangkan Anda sedang mengatur sebuah pesta besar (karena siapa yang tidak suka dengan analogi yang bagus?). Anda memiliki daftar tugas, dan Anda ingin memecahnya menjadi bagian yang lebih kecil dan mudah ditangani. Itu sebenarnya apa yang dilakukan Ekspresi Tabel Umum di SQL – itu membantu kita memecah query yang kompleks menjadi bagian yang lebih sederhana dan mudah dibaca.

Sebuah CTE adalah seperti sebuah hasil set temporer yang dinamai yang Anda bisa referensikan dalam SELECT, INSERT, UPDATE, DELETE, atau MERGE statement. Itu didefinisikan dalam lingkup eksekusi dari satu statement saja. Bayangkan itu seperti membuat sebuah tabel temporer yang ada hanya untuk query Anda.

mari kita lihat contoh sederhana:

WITH cte_example AS (
SELECT 'Hello, CTE!' AS greeting
)
SELECT greeting FROM cte_example;

Dalam contoh ini:

1. Kita mulai dengan kata kunci WITH, yang menandakan awal CTE kita.
2. Kita memberi CTE kita sebuah nama: cte_example.
3. Kita mendefinisikan apa yang akan berisi CTE kita: dalam kasus ini, sebuah SELECT statement yang membuat kolom 'greeting' dengan nilai 'Hello, CTE!'.
4. Setelah definisi CTE, kita memiliki query utama yang menggunakan CTE.

Ketika Anda menjalankan ini, Anda akan melihat:

| greeting    |
|-------------|
| Hello, CTE! |

Apakah itu menarik? Kita baru saja membuat CTE pertama kita!

Klauzula WITH di MySQL

Sekarang, mari bicarakan tentang klauzula WITH di MySQL. Itu adalah tongkat ajaib yang membuat CTE kita hidup. Sintaks umumnya tampak seperti ini:

WITH cte_name [(column_list)] AS (query)
SELECT * FROM cte_name;

mari kita lihat contoh yang lebih praktis. mari kita katakan kita memiliki tabel karyawan:

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finance', 55000),
(4, 'David', 'IT', 65000),
(5, 'Eve', 'HR', 52000);

Sekarang, mari kita gunakan CTE untuk mencari rata-rata gaji per departemen:

WITH dept_avg_salary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM dept_avg_salary
ORDER BY avg_salary DESC;

Query ini akan memberikan kita:

| department | avg_salary |
|------------|------------|
| IT         | 62500.00   |
| Finance    | 55000.00   |
| HR         | 51000.00   |

Di sini, kita menggunakan CTE untuk menghitung rata-rata gaji untuk setiap departemen, dan kemudian kita memilih dari CTE ini untuk menampilkan hasilnya. Itu seperti kita membuat tabel temporer dengan rata-rata gaji, yang kita gunakan dalam query utama kita.

CTE dari Banyak Tabel

CTE tidak terbatas hanya untuk satu tabel. Kita bisa menggunakan banyak tabel dalam definisi CTE kita, sama seperti dalam query biasa. mari kita tambahkan tabel departemen ke contoh kita:

CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50),
location VARCHAR(50)
);

INSERT INTO departments VALUES
(1, 'HR', 'New York'),
(2, 'IT', 'San Francisco'),
(3, 'Finance', 'Chicago');

Sekarang, mari kita gunakan CTE untuk menggabungkan informasi dari kedua tabel:

WITH dept_info AS (
SELECT e.department,
AVG(e.salary) AS avg_salary,
d.location
FROM employees e
JOIN departments d ON e.department = d.name
GROUP BY e.department, d.location
)
SELECT * FROM dept_info
ORDER BY avg_salary DESC;

Ini akan memberikan kita:

| department | avg_salary | location      |
|------------|------------|---------------|
| IT         | 62500.00   | San Francisco |
| Finance    | 55000.00   | Chicago       |
| HR         | 51000.00   | New York      |

Dalam contoh ini, CTE kita menggabungkan tabel employees dan departments, menghitung rata-rata gaji, dan termasuk informasi lokasi.

CTE Rekursif

Sekarang, mari kita masuk ke bagian yang sangat menarik – CTE rekursif! Ini seperti boneka Rusia yang bersarang. Sebuah CTE rekursif merujuk ke dirinya sendiri, memungkinkan Anda menulis query rekursif, yang sangat cocok untuk data hierarki atau struktur pohon.

mari kita buat contoh sederhana dari hierarki karyawan:

CREATE TABLE employee_hierarchy (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);

INSERT INTO employee_hierarchy VALUES
(1, 'Big Boss', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);

Sekarang, mari kita gunakan CTE rekursif untuk menampilkan seluruh hierarki:

WITH RECURSIVE emp_hierarchy AS (
SELECT id, name, manager_id, 0 AS level
FROM employee_hierarchy
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employee_hierarchy e
JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM emp_hierarchy
ORDER BY level, id;

Query ini akan menghasilkan:

| id | name       | manager_id | level |
|----|------------|------------|-------|
| 1  | Big Boss   | NULL       | 0     |
| 2  | Manager A  | 1          | 1     |
| 3  | Manager B  | 1          | 1     |
| 4  | Employee 1 | 2          | 2     |
| 5  | Employee 2 | 2          | 2     |
| 6  | Employee 3 | 3          | 2     |

CTE rekursif ini dimulai dengan karyawan tingkat atas (Big Boss) dan kemudian secara rekursif menemukan semua karyawan yang melaporkan kepada setiap manajer.

Keuntungan CTE

CTE datang dengan beberapa keuntungan:

Keuntungan	Deskripsi
Bacaan	CTE membuat query kompleks lebih mudah dibaca dengan memecahnya menjadi subquery yang dinamai.
Reusabilitas	Anda bisa referensikan CTE beberapa kali dalam sebuah query.
Rekursi	CTE memungkinkan Anda menulis query rekursif, yang sangat cocok untuk data hierarki.
Penyederhanaan	Mereka bisa menyederhanakan join dan subquery yang kompleks.
Perawatan	CTE membuat query lebih mudah untuk diperawat dan dimodifikasi.

Kerugian CTE

Meskipun CTE sangat kuat, mereka memiliki beberapa batasan:

Kerugian	Deskripsi
Performansi	Dalam beberapa kasus, CTE mungkin tidak performs secara baik seperti tabel turunan atau view.
Lingkup	CTE hanya valid dalam lingkup statement tunggal tempat mereka didefinisikan.
Kompleksitas	Untuk query yang sangat sederhana, penggunaan CTE mungkin menambah kompleksitas yang tidak diperlukan.
Dukungan Database	Tidak semua sistem database mendukung CTE, meskipun sebagian besar sistem modern melakukan itu.

Dan itu adalah dia, teman-teman! Kita telah mengemban perjalanan ke negeri Ekspresi Tabel Umum, dari konsep dasar hingga query rekursif. Ingat, seperti belajar keterampilan baru, memahami CTE memerlukan latihan. Jadi jangan khawatir untuk mencoba mereka dalam query Anda sendiri. Sebelum Anda tahu, Anda akan menulis CTE seperti seorang pro, mengimpresskan teman kerja Anda dan membuat query basis data Anda lebih bersih dan efisien. Selamat mencoba!

Credits: Image by storyset