Menulis Ulang IN, EXISTS, dan Subquery Berkorelasi

Data yang dipakai di artikel ini — perf_sales dan employee

Dua kueri yang mengembalikan hasil sama tetap bisa menghasilkan query plan yang berbeda (langkah-langkah yang dipilih database untuk tabel mana dibaca dalam urutan apa, dan indeks mana yang dipakai).

Di artikel ini kamu akan belajar kapan memakai IN versus EXISTS, cara menghilangkan subquery berkorelasi (subquery yang dievaluasi ulang untuk setiap baris luar), dan cara menulis ulang "simpan hanya baris yang punya (atau tidak punya) kecocokan" — sambil membandingkan query plan dengan EXPLAIN QUERY PLAN.

Sebelum masuk ke latihan, mari kita cek definisi kolom dan data sampel untuk dua tabel yang dipakai di artikel ini — perf_sales dan employee.

① Pakai PRAGMA table_info(perf_sales); dan PRAGMA table_info(employee); untuk mengonfirmasi definisi kolom kedua tabel.

② Pakai SELECT * FROM perf_sales LIMIT 5; dan SELECT * FROM employee LIMIT 5; untuk melihat 5 baris pertama dari masing-masing. perf_sales punya 50.000 baris, jadi selalu tambahkan LIMIT saat melihat pratinjau.

IN dan EXISTS — dua cara menulis cek yang sama

Ada dua cara untuk menanyakan "apakah ada baris yang cocok di tabel lain?".

WHERE col IN (SELECT ...) memeriksa apakah nilai ada di himpunan yang dikembalikan kueri dalam, sedangkan WHERE EXISTS (SELECT 1 FROM ... WHERE ...) memeriksa apakah kueri dalam mengembalikan setidaknya satu baris.

Di banyak kasus keduanya menghasilkan hasil yang sama, dan database bisa memperlakukan keduanya sebagai "simpan hanya baris yang punya kecocokan".

Menambahkan EXPLAIN QUERY PLAN query; di depan menampilkan langkah-langkah yang dipilih database, baris demi baris.

SCAN perf_sales berarti penelusuran seluruh baris, SEARCH ... USING INDEX berarti pergi langsung ke baris yang dibutuhkan lewat indeks, dan CORRELATED SCALAR SUBQUERY adalah subquery yang dievaluasi ulang untuk setiap baris luar.

-- Contoh: hitung penjualan yang ditangani karyawan di dept_id=2, dua cara

-- Bentuk IN
SELECT COUNT(*) FROM perf_sales
WHERE emp_id IN (SELECT emp_id FROM employee WHERE dept_id = 2);

-- Bentuk EXISTS (berkorelasi)
SELECT COUNT(*) FROM perf_sales p
WHERE EXISTS (
  SELECT 1 FROM employee e
  WHERE e.emp_id = p.emp_id AND e.dept_id = 2
);

-- Tambahkan EXPLAIN QUERY PLAN di depan untuk melihat plan
-- EXPLAIN QUERY PLAN SELECT COUNT(*) FROM perf_sales WHERE ...;

Mari lebih konkret. Bagaimana IN dan EXISTS berperilaku tergantung pada ukuran tabel luar dan ukuran subquery dalam serta apakah ada indeks?

Kedua kasus di bawah — dan bentuk mana yang lebih cepat di masing-masing — diringkas di diagram yang menyusul.

-- Kasus A: luar perf_sales (50.000 baris), dalam dept_id=1 employees (~5 baris, kecil dan tetap)

-- Bentuk IN
SELECT * FROM perf_sales
WHERE emp_id IN (SELECT emp_id FROM employee WHERE dept_id = 1);

-- Bentuk EXISTS
SELECT * FROM perf_sales p
WHERE EXISTS (
  SELECT 1 FROM employee e
  WHERE e.emp_id = p.emp_id AND e.dept_id = 1
);

-- Kasus B: luar employee (30 baris), dalam perf_sales (50.000 baris, dengan indeks pada emp_id)

-- Bentuk IN
SELECT * FROM employee e
WHERE e.emp_id IN (SELECT emp_id FROM perf_sales);

-- Bentuk EXISTS
SELECT * FROM employee e
WHERE EXISTS (
  SELECT 1 FROM perf_sales p
  WHERE p.emp_id = e.emp_id
);

Tip — database modern hampir tidak menunjukkan perbedaan kecepatan nyata

Kita sudah mengelompokkan hal-hal menjadi "IN cenderung menang / EXISTS cenderung menang", tapi itu kecenderungan yang berakar dari optimizer lama.

Di database modern seperti SQLite, PostgreSQL, dan MySQL 8.0+, baik IN (SELECT ...) maupun EXISTS berkorelasi ditulis ulang ke plan "simpan hanya baris yang punya kecocokan" yang sama, dan plan aktual serta kecepatan dunia nyata berakhir kurang lebih sama.

Dengan kata lain, kamu bisa memilih yang lebih enak dibaca.

Saat perbedaan plan atau perbedaan kecepatan benar-benar penting, aturannya adalah mengukur — EXPLAIN QUERY PLAN dan wall-clock time.

Tulis "hitungan perf_sales yang ditangani karyawan dengan dept_id 2" dalam dua bentuk — IN dan EXISTS — dan bandingkan masing-masing query plan. (Jalankan dengan benar untuk memunculkan penjelasan.)

① Tambahkan EXPLAIN QUERY PLAN di depan untuk menampilkan plan kueri hitungan dalam bentuk emp_id IN (SELECT ...).

② Lalu tampilkan plan untuk hitungan yang sama yang ditulis dalam bentuk EXISTS (mengkorelasi dengan employee) — lagi dengan EXPLAIN QUERY PLAN.

③ Bandingkan kedua plan, perhatikan kata-kata SCAN / SEARCH / USING INDEX dan seterusnya.

Menulis ulang subquery berkorelasi sebagai JOIN + agregasi

Subquery berkorelasi di daftar SELECT (yang dievaluasi ulang per baris luar) dipanggil lebih dan lebih sering seiring jumlah baris bertambah.

Di EXPLAIN QUERY PLAN ia muncul sebagai baris CORRELATED SCALAR SUBQUERY.

Di banyak kasus ia bisa ditulis ulang sebagai satu agregasi yang disiapkan sebelumnya dan di-join — evaluasi ulang per baris hilang.

Pola penulisan ulang mengubah "untuk setiap baris luar, panggil (SELECT ... WHERE child.key = parent.key)" menjadi "bangun tabel agregasi sekali dengan GROUP BY key, lalu JOIN ke parent".

Hasilnya sama dan korelasi hilang dari plan.

-- Sebelum: total penjualan per karyawan via subquery berkorelasi
SELECT e.emp_id, e.name,
  (SELECT SUM(p.amount) FROM perf_sales p
   WHERE p.emp_id = e.emp_id) AS total
FROM employee e
ORDER BY e.emp_id;

-- Sesudah: bangun agregasi sekali dan JOIN
WITH sales_sum AS (
  SELECT emp_id, SUM(amount) AS total
  FROM perf_sales
  GROUP BY emp_id
)
SELECT e.emp_id, e.name, s.total
FROM employee e
LEFT JOIN sales_sum s ON s.emp_id = e.emp_id
ORDER BY e.emp_id;

Jalankan "daftar total perf_sales setiap karyawan" dua cara — sebagai subquery berkorelasi dan sebagai penulisan ulang — dan lihat bagaimana plan berubah.

① Tambahkan EXPLAIN QUERY PLAN di depan untuk menampilkan plan versi yang menaruh (SELECT SUM(amount) FROM perf_sales WHERE emp_id sama) di daftar SELECT sebagai subquery berkorelasi.

② Lipat agregasi yang sama ke dalam CTE yang melakukan GROUP BY pada emp_id sekali, LEFT JOIN ke employee, dan tampilkan plan itu juga dengan EXPLAIN QUERY PLAN.

③ Konfirmasi bahwa baris CORRELATED SCALAR SUBQUERY di versi berkorelasi hilang di versi yang ditulis ulang.

Jebakan NULL NOT IN — hindari dengan NOT EXISTS

Saat kamu menanyakan "tidak ada baris yang cocok" — anti-join — NOT IN (SELECT ...) punya jebakan.

Kalau bahkan satu NULL menyelinap ke himpunan dalam, logika tiga nilai (TRUE / FALSE / UNKNOWN) membuat seluruh kondisi UNKNOWN, dan hasilnya kembali sebagai 0 baris.

Sementara itu, NOT EXISTS (SELECT 1 ... WHERE ...) hanya menanyakan apakah dalam menghasilkan baris, jadi NULL tidak merusaknya dan anti-join bekerja dengan benar.

Saat menulis anti-join, pakai NOT EXISTS — atau, kalau kamu harus memakai NOT IN, tambahkan WHERE col IS NOT NULL di dalam untuk membuang NULL.

Kursus ini memakai NOT EXISTS sebagai bentuk standar.

-- Konstruksi contoh dengan NULL di himpunan dalam, memakai team
-- team(mgr_emp) sengaja menyertakan satu NULL
WITH team(mgr_emp) AS (
  VALUES (3), (7), (NULL)
)
-- Jebakan: dengan NULL hadir, NOT IN mengembalikan 0 baris
SELECT COUNT(*) FROM perf_sales
WHERE emp_id NOT IN (SELECT mgr_emp FROM team);

-- Perbaikan: NOT EXISTS tidak terpengaruh oleh NULL, jadi anti-join bekerja
WITH team(mgr_emp) AS (
  VALUES (3), (7), (NULL)
)
SELECT COUNT(*) FROM perf_sales p
WHERE NOT EXISTS (
  SELECT 1 FROM team t WHERE t.mgr_emp = p.emp_id
);

Amati — lewat hitungan baris — apa yang terjadi saat himpunan dalam berisi NULL, membandingkan NOT IN dan NOT EXISTS. Ini latihan terakhir artikel. Bangun daftar nilai kecil bernama team (tiga nilai: 3, 7, NULL) sebagai CTE, lalu hitung "baris perf_sales yang emp_id-nya tidak ada di daftar itu" dua cara.

① Bangun CTE dengan 3, 7, NULL memakai WITH team(mgr_emp) AS (VALUES ...), dan hitung baris dengan emp_id NOT IN (SELECT mgr_emp FROM team).

② Tulis cek yang sama sebagai NOT EXISTS (SELECT 1 FROM team t WHERE t.mgr_emp = p.emp_id) dan hitung baris.

③ Bandingkan kedua hitungan dan konfirmasi bahwa versi NOT IN adalah 0.