CTE: kueri multi-langkah dengan WITH

Kueri nyata cenderung bertambah: tiga JOIN, sebuah GROUP BY, sebuah subkueri, subkueri lain… dan menjadi tidak dapat dibaca. CTE ("Tabel Umum Ekspresi", klausa WITH) memungkinkan Anda memberi nama pada langkah perantara dan gunakan seolah-olah itu adalah tabel di kueri utama.

Sintaks dasar

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

CTE valid hanya untuk kueri berikutnya: CTE tidak bertahan eksekusi.

Contoh

Temukan 3 pelanggan yang melakukan pembelanjaan paling banyak (jumlah dari pesanan yang dikirim):

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

Baca dalam bahasa Inggris: "pertama hitung pembelanjaan per pelanggan; lalu bergabunglah pelanggan dan ambil 3" teratas. Tanpa CTE Anda memerlukan inline subquery di FROM (legal, tapi lebih berisik).

Beberapa CTE dalam kaskade

Anda dapat menentukan beberapa CTE yang dipisahkan dengan koma, dan masing-masing CTE dapat merujuk ke yang sebelumnya:

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average karena average selalu memiliki satu baris: Produk Cartesian hanya menambahkan total_average ke setiap baris hasil.

CTE vs subquery: kapan menggunakan yang mana

CTE bersinar ketika:

• subkueri yang sama digunakan dua kali atau lebih dalam kueri utama;
• Anda ingin membuat struktur eksplisit dalam langkah-langkah yang disebutkan (mendokumentasikan sendiri);
• ini adalah prasyarat untuk CTE rekursif (WITH RECURSIVE, pelajaran berikutnya dalam modul ini).

Subkueri sebaris lebih ringkas untuk langkah yang hanya digunakan sekali.

Cobalah

Tinjau latihan