Hari 24: CTE — Common Table Expressions

CTE — Query Sementara yang Bisa Diberi Nama

CTE (WITH clause) membuat "tabel sementara" yang hanya hidup selama satu query. Lebih mudah dibaca daripada subquery bertingkat.

-- Syntax dasar
WITH nama_cte AS (
  SELECT ...
)
SELECT * FROM nama_cte;

-- Contoh: Top pelanggan
WITH ringkasan_pelanggan AS (
  SELECT
    pelanggan_id,
    COUNT(*) AS jumlah_pesanan,
    SUM(total_harga) AS total_belanja
  FROM pesanan
  GROUP BY pelanggan_id
)
SELECT
  pl.nama,
  rc.jumlah_pesanan,
  rc.total_belanja
FROM pelanggan pl
INNER JOIN ringkasan_pelanggan rc ON pl.id = rc.pelanggan_id
ORDER BY rc.total_belanja DESC;

Multiple CTE

WITH cte1 AS (...),
     cte2 AS (... FROM cte1 ...)
SELECT * FROM cte2;