Pada chapter IN dan NOT IN, daftar nilai yang kita bandingkan selalu ditulis manual — IN ('kopi', 'teh') berisi teks yang kita ketik sendiri. Di data nyata, daftar semacam itu sering justru hasil query lain: “produk yang pernah masuk pesanan mana pun” membutuhkan daftar produk_id yang diambil dari tabel transaksi, bukan diketik dari ingatan. Pada chapter ini kita memindahkan subquery ke klausa WHERE sebagai penghasil daftar nilai: operator IN dan ANY, operator ALL yang kita bedah dengan angka konkret, agregat sebagai ambang, lalu satu jebakan terkenal — NOT IN yang menghasilkan kosong karena satu baris NULL di hasil subquery.

Menyiapkan Empat Tabel Latihan #

Kita naikkan kompleksitas data: database belajar_sub kini memuat empat tabel toko daring yang akan dipakai konsisten sampai akhir section. Tabel produk masih sama, ditambah pelanggan, pesanan, dan detail_pesanan yang saling terhubung lewat kolom-kolom id:

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

Output yang Dihasilkan:

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

Query OK, 1 row affected (0.00 sec)

Database changed
sql 
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 pelanggan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL
);

CREATE TABLE pesanan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tanggal DATE NOT NULL,
  pelanggan INT 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 pelanggan (nama) VALUES
  ('Budi'), ('Siti'), ('Rina'), ('Andi'), ('Dewi');

INSERT INTO pesanan (tanggal, pelanggan) VALUES
  ('2026-01-05', 1),
  ('2026-01-06', 2),
  ('2026-01-08', 1),
  ('2026-01-10', 3),
  ('2026-01-12', 2),
  ('2026-01-15', NULL);

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:

text 
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

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

Tabel pesanan menyimpan id pelanggan pada kolom pelanggan; bila nilainya NULL, pesanan itu dibayar tunai tanpa pelanggan terdaftar. Mari kita pastikan baris NULL tersebut benar-benar ada, karena ia akan menjadi bintang utama jebakan NOT IN di bagian akhir chapter:

sql 
SELECT * FROM pesanan ORDER BY id;

Output yang Dihasilkan:

text 
+----+------------+-----------+
| id | tanggal    | pelanggan |
+----+------------+-----------+
|  1 | 2026-01-05 |         1 |
|  2 | 2026-01-06 |         2 |
|  3 | 2026-01-08 |         1 |
|  4 | 2026-01-10 |         3 |
|  5 | 2026-01-12 |         2 |
|  6 | 2026-01-15 |      NULL |
+----+------------+-----------+
6 rows in set (0.00 sec)

Lima pelanggan terdaftar, enam pesanan, sembilan baris detail. Pelanggan Andi dan Dewi belum pernah memesan; produk Teh Hijau dan Susu Kental belum pernah masuk transaksi.

Daftar Nilai dari Query Lain: IN #

Subquery di IN menggantikan daftar yang sebelumnya kita ketik manual. Query berikut mencari produk yang id-nya pernah muncul sebagai produk_id di tabel detail_pesanan:

sql 
SELECT id, nama FROM produk WHERE id IN (SELECT produk_id FROM detail_pesanan) ORDER BY id;

Output yang Dihasilkan:

text 
+----+-------------+
| id | nama        |
+----+-------------+
|  1 | Kopi Gayo   |
|  2 | Kopi Toraja |
|  3 | Teh Melati  |
|  5 | Gula Aren   |
+----+-------------+
4 rows in set (0.00 sec)

Mari kita bedah alurnya: subquery SELECT produk_id FROM detail_pesanan berjalan lebih dulu dan menghasilkan daftar 1, 2, 3, 5, 1, 2, 3, 5, 2 — sembilan nilai karena setiap baris detail ikut terambil. Operator IN hanya peduli keanggotaan, sehingga duplikasi di dalam daftar tidak merusak apa pun; baris produk lolos bila id-nya ditemukan sekali saja. Empat produk lolos: Kopi Gayo, Kopi Toraja, Teh Melati, dan Gula Aren. Dibanding versi dua chapter lalu, daftar ini selalu mutakhir — produk baru yang besok masuk transaksi otomatis ikut terhitung tanpa kita menyentuh query lagi.

= ANY, Padanan Resmi IN #

MySQL mengenal operator ANY (sinonimnya SOME) yang membandingkan sebuah nilai terhadap setiap anggota hasil subquery dan mengembalikan benar bila minimal satu perbandingan berhasil. = ANY karenanya identik dengan IN:

sql 
SELECT id, nama FROM produk WHERE id = ANY (SELECT produk_id FROM detail_pesanan) ORDER BY id;

Output yang Dihasilkan:

text 
+----+-------------+
| id | nama        |
+----+-------------+
|  1 | Kopi Gayo   |
|  2 | Kopi Toraja |
|  3 | Teh Melati  |
|  5 | Gula Aren   |
+----+-------------+
4 rows in set (0.00 sec)

Hasilnya persis sama dengan IN — empat produk yang sama. Dalam praktik sehari-hari, IN lebih sering dipilih karena lebih ringkas dan lebih dikenali pembaca query; ANY baru terasa berguna ketika pembandingnya bukan =, seperti > ANY yang kita bedah berikutnya.

ALL versus ANY dengan Angka Konkret #

Kedua operator ini paling mudah dipahami lewat angka. Produk teh di tabel kita ada dua: Teh Melati seharga 25000 dan Teh Hijau seharga 30000. Query berikut mencari produk yang harganya mengalahkan semua harga teh:

sql 
SELECT nama, harga FROM produk WHERE harga > ALL (SELECT harga FROM produk WHERE kategori = 'teh') ORDER BY harga;

Output yang Dihasilkan:

text 
+-------------+----------+
| nama        | harga    |
+-------------+----------+
| Kopi Gayo   | 55000.00 |
| Kopi Toraja | 62000.00 |
+-------------+----------+
2 rows in set (0.00 sec)

Sekarang versi ANY — cukup mengalahkan salah satu harga teh:

sql 
SELECT nama, harga FROM produk WHERE harga > ANY (SELECT harga FROM produk WHERE kategori = 'teh') ORDER BY harga;

Output yang Dihasilkan:

text 
+-------------+----------+
| nama        | harga    |
+-------------+----------+
| Teh Hijau   | 30000.00 |
| Kopi Gayo   | 55000.00 |
| Kopi Toraja | 62000.00 |
+-------------+----------+
3 rows in set (0.00 sec)

Beda satu kata, beda hasil: ALL menghasilkan dua baris, ANY menghasilkan tiga baris dengan Teh Hijau ikut masuk. Bedahnya dengan angka konkret:

  1. harga > ALL (25000, 30000) berarti harga harus lebih besar dari 25000 dan lebih besar dari 30000. Ambang yang benar-benar menentukan adalah yang tertinggi, jadi kondisi ini setara dengan harga > 30000 — hanya kedua produk kopi (55000 dan 62000) yang lolos.
  2. harga > ANY (25000, 30000) berarti harga cukup lebih besar dari 25000 atau lebih besar dari 30000. Ambang yang mudah dipenuhi adalah yang terendah, jadi kondisi ini setara dengan harga > 25000 — Teh Hijau (30000) ikut lolos bersama kedua kopi.
  3. Teh Hijau adalah kunci bedanya — harganya mengalahkan Teh Melati (25000) tetapi tidak mengalahkan dirinya sendiri (30000), sehingga ia lolos di ANY dan gugur di ALL.
KondisiSetara denganMakna
x > ALL (subquery)x > MAX(...)x harus melewati nilai tertinggi
x > ANY (subquery)x > MIN(...)x cukup melewati nilai terendah
x = ANY (subquery)x IN (...)x harus sama dengan salah satu anggota
x NOT IN (subquery)x <> ALL (subquery)x tidak boleh sama dengan satu pun anggota

Ambang dari Agregat #

Kombinasi favorit berikutnya: agregat di dalam subquery sebagai ambang penyaringan. Query ini menampilkan baris detail yang jumlah-nya di atas rata-rata jumlah seluruh baris detail — rata-rata dari 2, 1, 3, 1, 2, 2, 1, 1, 4, dan 1 adalah 1.8:

sql 
SELECT pesanan_id, produk_id, jumlah FROM detail_pesanan WHERE jumlah > (SELECT AVG(jumlah) FROM detail_pesanan) ORDER BY pesanan_id, produk_id;

Output yang Dihasilkan:

text 
+------------+-----------+--------+
| pesanan_id | produk_id | jumlah |
+------------+-----------+--------+
|          1 |         1 |      2 |
|          2 |         3 |      3 |
|          4 |         2 |      2 |
|          4 |         3 |      2 |
|          5 |         5 |      4 |
+------------+-----------+--------+
5 rows in set (0.00 sec)

Lima baris dengan jumlah 2 ke atas lolos. Pola ini bekerja karena AVG dijamin menghasilkan tepat satu nilai (scalar subquery), sehingga aman dipasangkan langsung dengan pembanding > — aturan yang sudah kita bangun di chapter Scalar Subquery. Kita sudah melihat variannya pada harga > (SELECT AVG(harga) FROM produk) di chapter pertama; bedanya, kini ambang berasal dari tabel yang berbeda dengan tabel yang disaring, suatu hal yang mustahil dilakukan satu query datar tanpa subquery.

NOT IN dan NULL: Hasil Kosong yang Diam-diam #

Sekarang jebakan yang dijanjikan. “Pelanggan yang belum pernah memesan” terasa wajar diterjemahkan menjadi NOT IN:

sql 
SELECT id, nama FROM pelanggan WHERE id NOT IN (SELECT pelanggan FROM pesanan);

Output yang Dihasilkan:

text 
Empty set (0.00 sec)

Hasilnya kosong, padahal Andi dan Dewi jelas-jelas tidak punya pesanan. Biang keroknya adalah pesanan id 6 — pesanan tunai dengan pelanggan bernilai NULL yang kita lihat di awal chapter. Daftar hasil subquerynya berbunyi (1, 2, 1, 3, 2, NULL), dan logika tiga nilai MySQL yang sudah kita bahas di chapter IN dan NOT IN berlaku: id NOT IN (..., NULL) tidak pernah menghasilkan benar — hasilnya NULL untuk setiap baris, dan hanya baris dengan kondisi benar yang lolos. Karena tak satu pun baris menghasilkan benar, hasil akhirnya kosong, tanpa error, tanpa peringatan.

Perbaikannya menutup lubang NULL di dalam subquery, bukan di query luar:

sql 
SELECT id, nama FROM pelanggan WHERE id NOT IN (SELECT pelanggan FROM pesanan WHERE pelanggan IS NOT NULL) ORDER BY id;

Output yang Dihasilkan:

text 
+----+------+
| id | nama |
+----+------+
|  4 | Andi |
|  5 | Dewi |
+----+------+
2 rows in set (0.00 sec)

Andi dan Dewi akhirnya muncul. Dengan WHERE pelanggan IS NOT NULL di dalam subquery, daftar yang dihasilkan bersih dari NULL sehingga NOT IN kembali berperilaku normal. Gotcha ini nyata di produksi: kolom referensi yang boleh kosong — pelanggan anonim, departemen yang belum ditentukan, produk yang sudah dihapus — cukup satu baris untuk melumpuhkan seluruh query NOT IN.

Sekilas: Subquery Berkorelasi di WHERE #

Satu varian terakhir untuk menggoda chapter berikutnya. Perhatikan subquery yang menyebut p.kategori — kolom milik tabel query luar:

sql 
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;

Subquery semacam ini disebut subquery berkorelasi (correlated subquery): ia dievaluasi ulang untuk setiap baris query luar, memakai nilai p.kategori baris tersebut sebagai acuan. Hasilnya tiga produk yang harganya di atas rata-rata kategorinya sendiri — Kopi Toraja di atas rata-rata kopi, Teh Hijau di atas rata-rata teh, Susu Kental di atas rata-rata pemanis. Mekanisme per-barisnya sengaja kita tangguhkan pembahasannya ke chapter khusus, karena konsep ini menjadi pondasi dua chapter setelahnya.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan database latihan kita jatuhkan kembali:

sql 
DROP DATABASE belajar_sub;

Output yang Dihasilkan:

text 
Query OK, 4 rows affected (0.00 sec)

Referensi #

Klausa WHERE kini bisa kita isi nilai scalar, daftar IN, pembanding ALL/ANY, hingga ambang agregat dari tabel lain. Posisi berikutnya justru yang paling jarang terpikir pemula: subquery diletakkan setelah FROM, di tempat biasanya nama tabel ditulis. Chapter Subquery dalam FROM membedah derived table — query hasil query yang diwajibkan punya alias.