Cara Menggunakan Klausa WITH Bersarang dalam SQL

Klausa WITH bersarang (nested) dalam SQL adalah alat yang kuat yang memungkinkan Anda membuat kueri yang kompleks yang sulit dicapai menggunakan metode lain. Dengan menyarangkan satu atau lebih Common Table Expressions (CTEs) di dalam CTE lain, Anda dapat membuat kueri yang merujuk pada hasil CTE sebelumnya dalam CTE berikutnya.

Untuk menggunakan klausa WITH bersarang dalam SQL, Anda pertama-tama perlu memahami dasar-dasar CTE. CTE adalah set hasil sementara yang didefinisikan dalam cakupan satu pernyataan SQL. Ini memungkinkan Anda untuk menentukan subkueri dan kemudian merujuk kepadanya nanti dalam kueri yang sama.

Berikut ini adalah contoh CTE sederhana yang menentukan subkueri yang menemukan semua karyawan yang telah bekerja di perusahaan selama lebih dari lima tahun:

WITH long_term_employees AS (
   SELECT employee_id, employee_name
   FROM employees
   WHERE hire_date < DATEADD(YEAR, -5, GETDATE())
)
SELECT *
FROM long_term_employees;

Untuk menyalin CTE, Anda dapat menentukan CTE kedua yang merujuk pada CTE pertama dalam definisinya. Berikut adalah contoh CTE bersarang yang menemukan semua karyawan yang telah bekerja di perusahaan selama lebih dari lima tahun dan memiliki gaji lebih dari $50.000:

WITH long_term_employees AS (
   SELECT employee_id, employee_name
   FROM employees
   WHERE hire_date < DATEADD(YEAR, -5, GETDATE())
), high_salary_employees AS (
   SELECT employee_id, employee_name
   FROM long_term_employees
   WHERE salary > 50000
)
SELECT *
FROM high_salary_employees;

Dalam contoh ini, CTE long_term_employees didefinisikan pertama dan CTE high_salary_employees didefinisikan kedua, menggunakan hasil CTE pertama dalam definisinya.

Dengan menyarangkan CTE dengan cara ini, Anda dapat membangun kueri yang kompleks yang merujuk pada beberapa subkueri dan set hasil sementara, sehingga lebih mudah untuk menulis dan memahami kueri SQL yang kompleks.

Contoh Nested WITH Clauses 3 tingkat dalam SQL

Berikut ini adalah contoh penggunaan nested WITH clause sebanyak 3 kali di SQL:

Misalkan Anda memiliki tabel yang disebut orders yang berisi data tentang pesanan pelanggan untuk perusahaan, termasuk tanggal pesanan, ID pelanggan, ID produk, dan jumlah yang dipesan. Anda ingin membuat kueri yang menampilkan total pendapatan untuk setiap kategori produk, dibagi berdasarkan bulan pesanan, dan lebih lanjut dibagi berdasarkan negara pelanggan.

Berikut adalah cara menggunakan nested WITH clause sebanyak 3 kali untuk mencapai ini:

WITH recursive revenue_by_month AS (
    SELECT
        product_categories.category_name,
        date_trunc('month', order_date) AS order_month,
        customers.country,
        sum(orders.quantity_ordered * products.price) AS revenue
    FROM orders
    JOIN products ON orders.product_id = products.product_id
    JOIN product_categories ON products.category_id = product_categories.category_id
    JOIN customers ON orders.customer_id = customers.customer_id
    GROUP BY 1, 2, 3
),
revenue_by_country AS (
    SELECT
        revenue_by_month.category_name,
        revenue_by_month.order_month,
        revenue_by_month.country,
        sum(revenue_by_month.revenue) AS total_revenue
    FROM revenue_by_month
    GROUP BY 1, 2, 3
),
revenue_by_category AS (
    SELECT
        revenue_by_country.order_month,
        revenue_by_country.country,
        product_categories.category_name,
        sum(revenue_by_country.total_revenue) AS monthly_total_revenue
    FROM revenue_by_country
    JOIN product_categories ON revenue_by_country.category_name = product_categories.category_name
    GROUP BY 1, 2, 3
)
SELECT
    revenue_by_category.order_month,
    revenue_by_category.country,
    revenue_by_category.category_name,
    revenue_by_category.monthly_total_revenue
FROM revenue_by_category
ORDER BY 1, 2, 3;

Pada kueri ini, bagian pertama dari nested WITH clause mendefinisikan common table expression yang disebut revenue_by_month. CTE ini menghitung pendapatan untuk setiap kategori produk, dibagi berdasarkan bulan pesanan dan negara pelanggan.

Bagian kedua dari nested WITH clause mendefinisikan CTE bernama revenue_by_country. CTE ini mengagregasi pendapatan berdasarkan kategori produk, bulan, dan negara.

Bagian ketiga dari nested WITH clause mendefinisikan CTE bernama revenue_by_category. CTE ini menghitung total pendapatan bulanan untuk setiap kategori produk, dibagi berdasarkan bulan pesanan dan negara pelanggan.

Akhirnya, pada kueri utama, kita memilih bulan pesanan, negara, nama kategori, dan total pendapatan bulanan dari CTE revenue_by_category dan mengurutkan hasilnya berdasarkan bulan, negara, dan kategori. Kueri ini akan mengembalikan sebuah set hasil yang menampilkan total pendapatan untuk setiap kategori produk, dibagi berdasarkan bulan pesanan dan negara pelanggan.