2023-03-04

Klausa SQL WITH

Apa itu klausa SQL WITH

Klausa SQL WITH, juga dikenal sebagai Common Table Expression (CTE), adalah fitur yang kuat yang memungkinkan Anda untuk mendefinisikan hasil sementara yang dinamai yang dapat digunakan dalam satu pernyataan SQL. Ini berarti Anda dapat memecah kueri kompleks menjadi bagian yang lebih kecil dan mudah dipahami dan dipelihara.

Klausa WITH juga dapat digunakan untuk membuat kueri rekursif, yang dapat digunakan untuk menjelajahi struktur data hierarkis.

Dengan menggunakan klausa SQL WITH, Anda dapat menyederhanakan kode SQL Anda dan membuatnya lebih efisien, sambil meningkatkan keterbacaan dan kemudahan pemeliharaannya.

Sintaksis Dasar dan Penggunaan

Sintaks dasar dari klausa SQL WITH adalah sebagai berikut:

sql

WITH cte_name AS
(
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name;

Berikut adalah contoh penggunaan klausa SQL WITH:

sql

WITH sales_data AS
(
    SELECT product_id, SUM(quantity) AS total_sales
    FROM orders
    WHERE order_date BETWEEN '2022-01-01' AND '2022-01-31'
    GROUP BY product_id
)
SELECT products.product_name, sales_data.total_sales
FROM products
INNER JOIN sales_data ON products.product_id = sales_data.product_id;

Pada contoh ini, klausa SQL WITH membuat sebuah hasil sementara bernama sales_data yang menghitung total penjualan untuk setiap produk selama bulan Januari 2022. Pernyataan SELECT utama kemudian bergabung dengan hasil set ini dengan tabel produk untuk mendapatkan nama produk dan total penjualannya untuk periode waktu tertentu.

Dengan menggunakan klausa SQL WITH, kueri yang kompleks dapat dipecah menjadi langkah-langkah yang lebih kecil dan mudah dikelola, membuat kode lebih mudah dibaca dan dipelihara. Selain itu, karena klausa WITH membuat hasil sementara yang dapat digunakan beberapa kali dalam satu kueri, ia juga dapat meningkatkan kinerja kueri dengan mengurangi jumlah data yang perlu diproses.

Perbandingan dengan Subquery

Subquery dan WITH clause keduanya digunakan dalam SQL untuk memecah kueri yang kompleks menjadi komponen yang lebih sederhana. Namun, ada beberapa perbedaan kunci antara keduanya.

Subquery adalah kueri yang tertanam dalam kueri lain. Mereka dapat digunakan pada berbagai bagian dari kueri, seperti klausa SELECT, FROM, dan WHERE. Subquery mengembalikan hasil set yang digunakan dalam kueri luar. Subquery dapat berhubungan atau tidak berhubungan.

Berikut adalah contoh subquery yang tidak berhubungan dalam klausa WHERE:

sql

SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date > '2023-01-01');

Pada contoh ini, subquery mengembalikan daftar ID pelanggan yang telah melakukan pesanan setelah 1 Januari 2023. Kueri luar kemudian menggunakan daftar ini untuk mengambil semua informasi tentang pelanggan tersebut dari tabel "customers".

Di sisi lain, klausa WITH, juga dikenal sebagai Common Table Expression (CTE), digunakan untuk mendefinisikan hasil set sementara yang dapat digunakan dalam konteks kueri yang lebih besar. WITH clause berguna ketika Anda perlu menggunakan kembali subquery beberapa kali dalam kueri yang lebih besar, atau ketika Anda perlu memecah kueri yang kompleks menjadi bagian yang lebih kecil dan mudah dikelola.

Berikut adalah contoh dari klausa WITH:

sql

WITH sales_data AS (
  SELECT customer_id, SUM(total_price) AS total_sales
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM sales_data
WHERE total_sales > 1000;

Dalam contoh ini, klausa WITH mendefinisikan hasil sementara yang disebut "sales_data" yang menghitung total penjualan untuk setiap pelanggan dari tabel "orders". Kueri luar kemudian menggunakan hasil sementara ini untuk hanya mengambil pelanggan yang total penjualannya melebihi 1000.

Perbedaan utama antara subkueri dan klausa WITH adalah subkueri digunakan untuk memecah kueri menjadi bagian yang lebih kecil dalam kueri yang sama, sedangkan klausa WITH digunakan untuk mendefinisikan hasil sementara yang dapat diacu beberapa kali dalam kueri yang lebih besar. Selain itu, subkueri dapat bersifat terkait atau tidak terkait, sedangkan klausa WITH selalu tidak terkait.

Kueri rekursif

Dalam SQL, klausa WITH dan kueri rekursif adalah fitur yang kuat yang memungkinkan Anda melakukan kueri hierarkis pada tabel yang memiliki hubungan induk-anak. Kueri rekursif adalah kueri yang merujuk pada dirinya sendiri dalam definisi kueri, sehingga menghasilkan hasil sementara yang bergantung pada hasil sementara dari kueri yang sama.

Berikut adalah contoh penggunaan kueri rekursif dengan klausa WITH dalam SQL:

Misalkan kita memiliki tabel yang disebut categories yang memiliki hubungan induk-anak. Setiap kategori dapat memiliki banyak subkategori, dan setiap subkategori dapat memiliki banyak kategori anak. Kita ingin memilih semua kategori dan subkategori mereka, terlepas dari seberapa dalam penanaman itu.

Pertama, kita mendefinisikan tabel tree sebagai ekspresi tabel umum rekursif (CTE) menggunakan kata kunci WITH RECURSIVE. Tabel tree berisi kolom id, parent_id, name, dan level, yang digunakan untuk membangun struktur hierarkis kategori.

sql

WITH RECURSIVE tree(id, parent_id, name, level) AS (
  SELECT id, parent_id, name, 1
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.level + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT *
FROM tree
ORDER BY level, name;

Query di atas dimulai dengan memilih semua baris yang memiliki parent_id NULL, menandakan bahwa mereka adalah kategori tingkat atas. Setelah itu, hasilnya diperluas secara rekursif untuk mencakup semua kategori anak dari kategori tingkat atas tersebut.

Operator UNION ALL digunakan untuk menggabungkan bagian non-rekursif dan rekursif dari query. Pada bagian non-rekursif, kita memilih kategori tingkat atas dari tabel categories. Pada bagian rekursif, kita bergabung dengan tabel categories dan tabel tree pada kolom id dan parent_id, memperluas hasil secara rekursif untuk mencakup semua kategori anak.

Terakhir, pernyataan SELECT luar memilih semua kolom dari tabel tree untuk menampilkan struktur hierarki kategori, diurutkan berdasarkan level dan nama.

Menggunakan klausa WITH dengan pernyataan UPDATE, INSERT, dan DELETE

Meskipun klausa WITH umumnya digunakan dengan pernyataan SELECT, tetapi juga dapat digunakan dengan pernyataan UPDATE, INSERT, dan DELETE.

Menggunakan klausa WITH dengan pernyataan UPDATE memungkinkan Anda untuk memperbarui sebuah tabel berdasarkan hasil dari CTE. Misalnya, katakanlah Anda ingin memperbarui gaji karyawan di sebuah departemen berdasarkan rata-rata gaji dari departemen tersebut. Anda dapat menggunakan CTE untuk menghitung rata-rata gaji dan kemudian memperbarui tabel berdasarkan hasil itu:

sql

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
UPDATE employees
SET salary = dept_avg.avg_salary
FROM dept_avg
WHERE employees.department = dept_avg.department;

Menggunakan klausa WITH dengan pernyataan INSERT memungkinkan Anda untuk memasukkan data ke dalam tabel berdasarkan hasil dari CTE. Sebagai contoh, katakanlah Anda memiliki tabel pesanan dan ingin memasukkan total penjualan setiap bulan ke dalam tabel baru. Anda dapat menggunakan CTE untuk menghitung total penjualan dan kemudian memasukkan data ke dalam tabel baru:

sql

WITH monthly_sales AS (
  SELECT date_trunc('month', order_date) AS month, SUM(price) AS total_sales
  FROM orders
  GROUP BY date_trunc('month', order_date)
)
INSERT INTO sales_by_month (month, total_sales)
SELECT month, total_sales
FROM monthly_sales;

Menggunakan klausa WITH dengan pernyataan DELETE memungkinkan Anda untuk menghapus data dari sebuah tabel berdasarkan hasil dari CTE. Sebagai contoh, katakanlah Anda memiliki tabel pelanggan dan ingin menghapus semua pelanggan yang tidak melakukan pemesanan dalam setahun terakhir. Anda dapat menggunakan CTE untuk menemukan pelanggan yang tidak melakukan pemesanan dalam setahun terakhir dan kemudian menghapusnya:

sql

WITH inactive_customers AS (
  SELECT customer_id
  FROM customers
  WHERE NOT EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
    AND order_date > now() - interval '1 year'
  )
)
DELETE FROM customers
WHERE customer_id IN (SELECT customer_id FROM inactive_customers);