Subquery, CTE, dan operasi himpunan

Subquery adalah query yang dipakai sebagai nilai, sebagai himpunan, atau sebagai tabel. Dia termasuk yang mana menentukan di mana dia boleh muncul dan berapa harganya.

Subquery skalar: dipakai sebagai nilai

Harus mengembalikan paling banyak satu baris dan satu kolom.

SELECT id, cents,
       cents - (SELECT AVG(cents) FROM product) AS vs_average
FROM product;

Kalau dia mengembalikan lebih dari satu baris kamu dapat error saat berjalan — di kedua mesin — dan itu bahaya nyata ketika datanya tumbuh ke kasus yang belum kamu pertimbangkan. Subquery skalar yang tidak mengembalikan baris malah menghasilkan NULL, jadi aritmetikanya diam-diam jadi NULL, bukan gagal. Dua mode kegagalan berbeda dari satu konstruksi.

IN / EXISTS: dipakai sebagai himpunan

SELECT * FROM customer
WHERE id IN (SELECT customer_id FROM "order" WHERE status = 'paid');

Utamakan EXISTS kalau subquery-nya bisa memuat NULL, dengan alasan di materi 2. Utamakan IN kalau daftarnya kecil dan literal — dia lebih enak dibaca.

Subquery berkorelasi merujuk baris luar, jadi secara konsep dia berjalan per baris:

SELECT c.id,
       (SELECT COUNT(*) FROM "order" o WHERE o.customer_id = c.id) AS orders
FROM customer c;

Kedua planner sering bisa menulis ulang ini jadi join atau agregat berkelompok, jadi “berjalan per baris” itu model mental, bukan janji soal eksekusi. Tapi penulisan ulang itu tidak dijamin, dan bentuk inilah yang berubah jadi pencarian per baris di tabel besar. Kalau kamu butuh beberapa hitungan semacam itu, satu LEFT JOIN ke subquery berkelompok mengalahkan lima subquery berkorelasi.

Derived table: dipakai sebagai tabel

Subquery di dalam FROM. MySQL mensyaratkan alias; PostgreSQL juga.

SELECT o.id, g.goods
FROM "order" o
JOIN (
  SELECT order_id, SUM(unit_cents * quantity) AS goods
  FROM order_item
  GROUP BY order_id
) g ON g.order_id = o.id;         -- alias g itu wajib

Ini bentuk agregasi-dulu-baru-join dari materi 3, dan dia pola subquery paling berguna yang ada.

WITH: hal yang sama, dengan nama

Common table expression adalah derived table yang diberi nama di depan, sehingga kamu bisa membaca query dari atas ke bawah alih-alih dari dalam ke luar.

WITH goods AS (
  SELECT order_id, SUM(unit_cents * quantity) AS cents
  FROM order_item
  GROUP BY order_id
),
big AS (
  SELECT order_id, cents FROM goods WHERE cents > 100000
)
SELECT o.id, c.full_name, big.cents
FROM big
JOIN "order" o    ON o.id = big.order_id
JOIN customer c   ON c.id = o.customer_id;

MySQL mendukung WITH sejak 8.0. Sebelum itu, derived table satu-satunya pilihan.

Pagar optimisasi

Ini bagian yang pengetahuannya sering sudah kedaluwarsa di kepala orang.

Jadi nasihat lama “CTE lambat di PostgreSQL” itu dulu benar dan sekarang bukan perilaku bawaan lagi. Dan nasihat kebalikannya — “CTE itu gratis” — juga salah, karena materialisasi tetap terjadi ketika CTE-nya dirujuk dua kali.

Sesekali kamu memang mau pagarnya:

-- Paksa CTE-nya berjalan sekali, tepat seperti yang ditulis
WITH expensive AS MATERIALIZED (
  SELECT ... sesuatu dengan fungsi mahal ...
)
SELECT * FROM expensive WHERE id = 42;

Tanpa MATERIALIZED, PostgreSQL 12+ boleh mendorong id = 42 ke dalam dan menjalankan fungsi mahal itu pada lebih sedikit baris — biasanya bagus, sesekali kebalikan dari yang kamu ukur.

CTE rekursif

Kedua mesin mendukung WITH RECURSIVE, dan bentuknya identik: sebuah benih non-rekursif, UNION ALL, lalu suku yang merujuk CTE-nya sendiri.

-- Seri tanggal: satu baris per hari di Januari 2026
WITH RECURSIVE days(d) AS (
  SELECT DATE '2026-01-01'
  UNION ALL
  SELECT d + 1 FROM days WHERE d < DATE '2026-01-31'
)
SELECT d FROM days;

MySQL menulis penambahannya berbeda:

WITH RECURSIVE days(d) AS (
  SELECT DATE '2026-01-01'
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM days WHERE d < DATE '2026-01-31'
)
SELECT d FROM days;

Kondisi berhentinya tanggung jawabmu. Hilangkan dia dan PostgreSQL berjalan sampai memori atau disknya habis; MySQL berhenti di cte_max_recursion_depth, bawaannya 1000, dengan error. Bawaan MySQL adalah kegagalan yang lebih ramah.

PostgreSQL punya alat jauh lebih sederhana untuk kasus seri: generate_series('2026-01-01'::date, '2026-01-31'::date, '1 day'). MySQL tidak punya padanannya, dan karena itulah bentuk rekursifnya pantas diketahui.

Kegunaan sebenarnya adalah hierarki — pohon kategori, garis pelaporan, order yang menggantikan order sebelumnya:

WITH RECURSIVE chain AS (
  SELECT id, replaces_order_id, 1 AS depth
  FROM "order" WHERE id = 1000
  UNION ALL
  SELECT o.id, o.replaces_order_id, chain.depth + 1
  FROM "order" o JOIN chain ON o.id = chain.replaces_order_id
)
SELECT * FROM chain;

(replaces_order_id tidak ada di skema kita — yang penting bentuknya.)

Jagalah terhadap siklus. Baris yang rantai induknya berputar membuat ini berjalan selamanya; perbaikan lazimnya adalah menumpuk id yang sudah dikunjungi ke dalam array (PostgreSQL) atau string tergabung (MySQL) lalu mengecualikannya.

Operasi himpunan

SELECT country FROM customer
EXCEPT
SELECT country FROM customer WHERE created_at >= '2026-01-01';
-- negara yang punya customer, tapi tidak punya yang baru tahun ini

Aturan yang berlaku untuk semuanya: kedua sisi harus punya jumlah kolom yang sama dengan tipe yang kompatibel, nama kolomnya diambil dari cabang pertama, dan ORDER BY tempatnya di paling akhir, berlaku untuk hasil gabungannya.

UNION ALL kecuali kamu memang bermaksud lain. UNION harus membuang duplikat, artinya mengurutkan atau meng-hash setiap baris dari kedua masukan. Kalau kamu tahu cabangnya saling lepas — dan biasanya kamu tahu, karena itu sebabnya kamu memisahkannya — UNION ALL melewati semua pekerjaan itu.

LATERAL: subquery yang melihat baris saat ini

LATERAL membuat derived table bisa merujuk kolom dari item sebelumnya di FROM yang sama. PostgreSQL dan MySQL 8.0.14+ keduanya mendukung.

-- Tiga order terbaru per customer
SELECT c.id, o.id, o.placed_at
FROM customer c
CROSS JOIN LATERAL (
  SELECT id, placed_at FROM "order"
  WHERE customer_id = c.id            -- hanya sah karena ada LATERAL
  ORDER BY placed_at DESC LIMIT 3
) o;

Tanpa LATERAL, c.id tidak terlihat di dalam subquery-nya. Masalah “N teratas per grup” ini juga punya jawaban lewat window function, yaitu materi berikutnya — bentuk LATERAL sering lebih cepat kalau N-nya kecil dan ada index yang mendukung ORDER BY-nya.

Yang perlu dibawa pulang

• Subquery skalar gagal berisik saat banyak baris dan diam-diam saat nol baris.
• Derived table butuh alias, di kedua mesin.
• CTE adalah subquery bernama. PostgreSQL 12+ meng-inline yang sekali pakai; MATERIALIZED memulihkan pagar lamanya.
• WITH butuh MySQL 8.0; INTERSECT / EXCEPT butuh 8.0.31.
• UNION ALL kecuali kamu benar-benar butuh pembuangan duplikat.
• LATERAL adalah cara subquery di FROM melihat baris saat ini.