Correlated Subquery

Subquery yang kita temui sejauh ini — dari rata-rata harga sampai daftar produk terjual — berdiri sendiri: ia bisa dijalankan terpisah, hasilnya dihitung sekali, lalu dipakai query luar. Ada satu keluarga subquery yang bekerja secara berbeda: subquery yang menyebut kolom milik tabel query luar di dalam kondisinya. Subquery semacam ini tidak bisa dijalankan sendiri, dan MySQL mengevaluasinya ulang untuk setiap baris luar — satu per satu, dengan nilai kolom yang berbeda-beda sebagai acuan. Keluarga ini bernama correlated subquery, dan tanpa sadar kita sudah memakainya dua kali: kolom kalkulasi di chapter Subquery dalam SELECT dan EXISTS di chapter sebelumnya. Pada chapter ini kita bedah mekanismenya secara sadar dan lengkap.

Menyiapkan Tabel Latihan #

Dua tabel cukup untuk seluruh kebutuhan kita: produk sebagai tabel luar dan detail_pesanan sebagai tabel acuan di dalam subquery. Data sama persis dengan chapter-chapter sebelumnya:

DROP DATABASE IF EXISTS belajar_sub;
CREATE DATABASE belajar_sub;
USE belajar_sub;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE produk (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL,
  kategori VARCHAR(30) NOT NULL,
  harga DECIMAL(10,2) NOT NULL
);

CREATE TABLE detail_pesanan (
  pesanan_id INT NOT NULL,
  produk_id INT NOT NULL,
  jumlah INT NOT NULL
);

INSERT INTO produk (nama, kategori, harga) VALUES
  ('Kopi Gayo', 'kopi', 55000.00),
  ('Kopi Toraja', 'kopi', 62000.00),
  ('Teh Melati', 'teh', 25000.00),
  ('Teh Hijau', 'teh', 30000.00),
  ('Gula Aren', 'pemanis', 15000.00),
  ('Susu Kental', 'pemanis', 18000.00);

INSERT INTO detail_pesanan (pesanan_id, produk_id, jumlah) VALUES
  (1, 1, 2),
  (1, 5, 1),
  (2, 3, 3),
  (3, 3, 1),
  (4, 2, 2),
  (4, 3, 2),
  (5, 1, 1),
  (5, 5, 4),
  (6, 2, 1);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

Rata-rata harga per kategori bila dihitung manual: kopi 58500 (dari 55000 dan 62000), teh 27500 (dari 25000 dan 30000), pemanis 16500 (dari 15000 dan 18000). Tiga angka ini akan menjadi alat verifikasi contoh utama kita.

Subquery yang Melihat Tabel Luar #

Pertanyaan “produk mana yang harganya di atas rata-rata?” sudah kita jawab di chapter pertama memakai rata-rata global. Versi yang lebih adil — dan lebih sering diminta bisnis — adalah rata-rata kategorinya sendiri: menandingi Kopi Toraja dengan rata-rata kopi, bukan dengan rata-rata seluruh toko yang didominasi pemanis murah:

SELECT p.nama, p.kategori, p.harga FROM produk p WHERE p.harga > (SELECT AVG(p2.harga) FROM produk p2 WHERE p2.kategori = p.kategori) ORDER BY p.kategori, p.nama;

Output yang Dihasilkan:

+-------------+----------+----------+
| nama        | kategori | harga    |
+-------------+----------+----------+
| Kopi Toraja | kopi     | 62000.00 |
| Susu Kental | pemanis  | 18000.00 |
| Teh Hijau   | teh      | 30000.00 |
+-------------+----------+----------+
3 rows in set (0.00 sec)

Tiga produk lolos: Kopi Toraja, Susu Kental, dan Teh Hijau. Sebelum membedah mekanismenya, mari kita pastikan angka acuannya dengan pengelompokan biasa:

SELECT kategori, AVG(harga) AS rata_harga FROM produk GROUP BY kategori ORDER BY kategori;

Output yang Dihasilkan:

+----------+--------------+
| kategori | rata_harga   |
+----------+--------------+
| kopi     | 58500.000000 |
| pemanis  | 16500.000000 |
| teh      | 27500.000000 |
+----------+--------------+
3 rows in set (0.00 sec)

Rata-rata per kategori terbukti: kopi 58500, pemanis 16500, teh 27500. Sekarang bedah baris-per-baris query utamanya:

1. FROM produk p — query luar berjalan di atas tabel produk; setiap barisnya diproses satu per satu.
2. p2.kategori = p.kategori — inilah tanda berkorelasinya: subquery (berjalan di atas alias p2, yang kebetulan tabel yang sama) menyebut p.kategori, kolom milik query luar. Subquery ini mustahil dijalankan sendirian — nilai p.kategori baru ada ketika baris luar sedang diproses.
3. Evaluasi per baris — saat MySQL memproses baris Kopi Gayo, subquery menghitung AVG hanya dari produk berkategorian kopi, yaitu 58500; 55000 tidak melewatinya, baris gugur. Baris berikutnya, Kopi Toraja, memicu penghitungan yang sama — subquery berjalan lagi dengan hasil 58500 — dan 62000 lolos.
4. Enam baris, beberapa eksekusi subquery — setiap baris produk mendapat satu eksekusi subquery dengan acuan kategorinya sendiri: dua kali rata-rata kopi, dua kali teh, dua kali pemanis. Bandingkan dengan subquery global di chapter pertama yang dievaluasi sekali untuk semua baris.

Hasil akhirnya masuk akal secara bisnis: Kopi Toraja adalah kopi termahal di kategorinya, Teh Hijau teh termahal di kategorinya, Susu Kental pemanis termahal di kategorinya — padahal ketiganya berada di posisi berbeda-beda bila dibandingkan dengan rata-rata global 34166.67.

EXISTS Berkorelasi #

Bentuk kedua yang sudah kita temui adalah EXISTS di chapter sebelumnya — mari kita baca ulang dengan kacamata baru, kali ini dengan kondisi tambahan agar contohnya segar. Query berikut mencari produk yang pernah dibeli lebih dari dua unit sekaligus dalam satu baris detail:

SELECT nama FROM produk WHERE EXISTS (SELECT 1 FROM detail_pesanan d WHERE d.produk_id = produk.id AND d.jumlah > 2) ORDER BY nama;

Output yang Dihasilkan:

+------------+
| nama       |
+------------+
| Gula Aren  |
| Teh Melati |
+------------+
2 rows in set (0.00 sec)

Gula Aren dan Teh Melati lolos — keduanya punya baris detail dengan jumlah 4 dan 3. Subquery di dalam EXISTS berkorelasi lewat d.produk_id = produk.id: untuk setiap baris produk, MySQL mencari baris detail milik produk itu yang memenuhi jumlah > 2, lalu EXISTS cukup menunggu ada atau tidaknya baris itu. Sebagaimana sudah kita bahas, EXISTS berhenti di baris pertama yang cocok — penghentian dini yang membuat subquery berkorelasi di balik EXISTS jauh lebih murah daripada menghitung agregat lengkap per baris.

Sanak Keluarganya di SELECT #

Kolom kalkulasi di chapter Subquery dalam SELECT sebenarnya correlated subquery juga:

SELECT p.nama, (SELECT COUNT(*) FROM detail_pesanan d WHERE d.produk_id = p.id) AS jumlah_pesanan FROM produk p ORDER BY p.id;

Output yang Dihasilkan:

+-------------+----------------+
| nama        | jumlah_pesanan |
+-------------+----------------+
| Kopi Gayo   |              2 |
| Kopi Toraja |              2 |
| Teh Melati  |              3 |
| Teh Hijau   |              0 |
| Gula Aren   |              2 |
| Susu Kental |              0 |
+-------------+----------------+
6 rows in set (0.00 sec)

Subquery (SELECT COUNT(*) ... WHERE d.produk_id = p.id) menyebut p.id — kolom luar — sehingga dievaluasi enam kali, sekali per produk, dan itulah sebabnya nilainya bisa berbeda per baris (2, 2, 3, 0, 2, 0). Tiga posisi yang tadinya kita perlakukan sebagai teknik terpisah — WHERE, SELECT, dan EXISTS — ternyata memakai satu mekanisme per-baris yang sama; yang berbeda hanya apa yang dilakukan dengan jawaban subquerynya: menyaring baris, mengisi kolom, atau memutuskan ada atau tidak.

Biaya per Baris di Tabel Besar #

Mekanisme per-baris ini punya sisi gelap yang perlu kita pegang sejak awal. Subquery berkorelasi dievaluasi ulang untuk setiap baris luar: tabel luar 10.000 baris berarti 10.000 eksekusi subquery. Bila setiap eksekusi memindai tabel acuan yang juga besar, total kerja melipat menjadi 10.000 kali lipat pemindaian — kombinasi yang membuat query berjalan menit-menit. Pada tabel latihan kita yang kecil, semua contoh berjalan dalam hitungan milidetik, sehingga pola ini terasa gratis; di produksi ia bisa menjadi sumber laporan yang “tiba-tiba” lambat.

Beberapa hal praktis untuk menjaga diri. Pertama, MySQL sering dapat menulis ulang (rewrite) subquery berkorelasi menjadi bentuk join atau derived table yang jauh lebih efisien — optimizer modern memang agresif — tetapi jaminan tidak selalu ada, jadi ukur dengan data dan volume nyata. Kedua, pertanyaan per-kategori seperti contoh utama kita punya padanan join ke derived table: agregat per kategori dihitung sekali di subquery FROM, lalu di-join — satu kali perhitungan untuk semua baris. Ketiga, EXISTS berkorelasi relatif murah karena berhenti di baris pertama; pola yang paling perlu diawasi adalah agregat berkorelasi di SELECT pada laporan besar, persis peringatan di chapter Subquery dalam SELECT.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan database latihan kita jatuhkan kembali:

DROP DATABASE belajar_sub;

Output yang Dihasilkan:

Query OK, 2 rows affected (0.00 sec)

Referensi #

• Subqueries — MySQL 8.4 Reference Manual
• EXISTS and NOT EXISTS Subqueries — MySQL 8.4 Reference Manual
• Aggregate Function Descriptions — MySQL 8.4 Reference Manual

Kita kini memegang dua cara berpikir untuk hampir setiap soal: join yang menggabungkan baris, dan subquery yang menyelipkan pertanyaan kecil. Banyak soal bisa dijawab keduanya — hasilnya sama, tetapi keterbacaan, performa, dan kecocokannya berbeda. Chapter penutup section ini, Subquery vs JOIN, mempertaruhkan kedua kubu pada pertanyaan-pertanyaan nyata dan menutupnya dengan tabel keputusan yang bisa kita tempel di meja kerja.