Subquery & CTE (WITH): Query di Dalam Query

Subquery: pertanyaan di dalam pertanyaan

Pertanyaan: "Buku mana yang lebih mahal dari harga rata-rata?" Itu dua langkah: (1) hitung rata-rata; (2) bandingkan. Subquery membiarkan kita menulis keduanya dalam satu perintah.

SELECT judul, harga
FROM buku
WHERE harga > (SELECT AVG(harga) FROM buku);

Query dalam kurung dikerjakan lebih dulu, hasilnya (satu angka) dipakai oleh query luar.

Subquery dengan IN

Bila subquery menghasilkan daftar nilai, pakai IN:

SELECT nama FROM pelanggan
WHERE id IN (SELECT pelanggan_id FROM pesanan WHERE status = 'lunas');

EXISTS: "apakah ada?"

SELECT nama FROM pelanggan pl
WHERE EXISTS (SELECT 1 FROM pesanan p WHERE p.pelanggan_id = pl.id AND p.status = 'batal');

EXISTS bernilai benar bila subquery menghasilkan minimal satu baris. Sering lebih cepat dari IN pada data besar, dan aman dari jebakan NULL (NOT EXISTS lebih aman daripada NOT IN).

Subquery di FROM (tabel sementara)

SELECT AVG(total) AS rata_total_pesanan
FROM (
  SELECT pesanan_id, SUM(jumlah * harga_satuan) AS total
  FROM detail_pesanan
  GROUP BY pesanan_id
) AS per_pesanan;

Subquery di FROM wajib diberi alias (di sini per_pesanan).

CTE: WITH, subquery yang rapi

Kalau subquery bertingkat membuat kepalamu pusing, pakai CTE (Common Table Expression): beri nama pada langkah pertama dulu, lalu pakai namanya seperti tabel.

WITH total_per_pesanan AS (
  SELECT pesanan_id, SUM(jumlah * harga_satuan) AS total
  FROM detail_pesanan
  GROUP BY pesanan_id
)
SELECT p.id, pl.nama, t.total
FROM pesanan p
JOIN pelanggan pl ON pl.id = p.pelanggan_id
JOIN total_per_pesanan t ON t.pesanan_id = p.id
WHERE t.total > 150000
ORDER BY t.total DESC;

Beberapa CTE sekaligus

WITH a AS (...), b AS (...)
SELECT ... FROM a JOIN b ON ...;

CTE juga bisa rekursif (WITH RECURSIVE), untuk data berbentuk pohon seperti struktur organisasi atau kategori bersarang.

Kasus nyata: buku yang harganya di atas rata-rata kategorinya

WITH rata AS (
  SELECT kategori_id, AVG(harga) AS rata_harga
  FROM buku GROUP BY kategori_id
)
SELECT b.judul, b.harga, ROUND(r.rata_harga) AS rata_kategori
FROM buku b
JOIN rata r ON r.kategori_id = b.kategori_id
WHERE b.harga > r.rata_harga;

Tanpa CTE, query ini menjadi subquery bersarang yang sulit dibaca dan dirawat.

Kesalahan yang sering terjadi

• Subquery yang mengembalikan banyak baris dipakai dengan =. Pakai IN atau ANY.
• Menumpuk subquery 4 tingkat. Pecah menjadi beberapa CTE bernama jelas.
• Mengira CTE selalu lebih cepat. Ia terutama untuk keterbacaan; ukur dengan EXPLAIN bila performa penting.