Cara Menggunakan Subquery di MySQL

Bayangkan Anda perlu mencari produk yang harganya di atas rata-rata harga semua produk. Atau mencari pelanggan yang pernah melakukan pembelian di atas nilai tertentu. Untuk kebutuhan seperti ini, Anda memerlukan Subquery — yaitu query yang ditulis di dalam query lainnya. Subquery adalah salah satu fitur SQL paling powerful yang wajib Anda kuasai. Artikel ini membahas cara menggunakannya di berbagai situasi.

Apa Itu Subquery?

Subquery (disebut juga inner query atau nested query) adalah SELECT yang ditempatkan di dalam query lain. Query yang "membungkus" subquery disebut outer query. Subquery selalu diapit tanda kurung () dan biasanya dieksekusi lebih dulu sebelum outer query.

Persiapan Data Contoh

CREATE TABLE produk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(150) NOT NULL,
    kategori VARCHAR(50),
    harga DECIMAL(10,2) NOT NULL,
    stok INT DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE penjualan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_produk INT NOT NULL,
    jumlah INT NOT NULL,
    tanggal DATE NOT NULL
) ENGINE=InnoDB;

INSERT INTO produk (nama, kategori, harga, stok) VALUES
('Laptop ASUS', 'Elektronik', 9500000, 10),
('Mouse Logitech', 'Elektronik', 350000, 50),
('Keyboard Mechanical', 'Elektronik', 750000, 30),
('Meja Kayu', 'Furnitur', 2200000, 5),
('Kursi Ergonomis', 'Furnitur', 3500000, 8),
('Buku MySQL', 'Buku', 95000, 100),
('Monitor 27"', 'Elektronik', 4800000, 15);

INSERT INTO penjualan (id_produk, jumlah, tanggal) VALUES
(1, 2, '2024-01-10'), (2, 10, '2024-01-11'),
(3, 5, '2024-01-12'), (1, 1, '2024-01-13'),
(5, 2, '2024-01-14'), (7, 3, '2024-01-15');

Subquery di Klausa WHERE

Penggunaan paling umum adalah menempatkan subquery di klausa WHERE untuk membandingkan nilai:

-- Cari produk yang harganya di atas rata-rata semua produk
SELECT nama, kategori, harga
FROM produk
WHERE harga > (SELECT AVG(harga) FROM produk)
ORDER BY harga DESC;

-- Cari produk dengan harga tertinggi di kategorinya
SELECT nama, kategori, harga
FROM produk
WHERE harga = (SELECT MAX(harga) FROM produk WHERE kategori = 'Elektronik')
AND kategori = 'Elektronik';

Subquery dengan IN dan NOT IN

Gunakan IN ketika subquery mengembalikan lebih dari satu nilai:

-- Cari produk yang pernah terjual
SELECT id, nama, harga
FROM produk
WHERE id IN (SELECT DISTINCT id_produk FROM penjualan);

-- Cari produk yang belum pernah terjual sama sekali
SELECT id, nama, harga
FROM produk
WHERE id NOT IN (SELECT DISTINCT id_produk FROM penjualan);

Subquery di Klausa FROM (Derived Table)

Subquery bisa dijadikan tabel sementara di klausa FROM. Ini disebut Derived Table:

-- Hitung total penjualan per produk, lalu filter yang di atas rata-rata
SELECT
    p.nama,
    ringkasan.total_terjual,
    ringkasan.total_pendapatan
FROM produk p
INNER JOIN (
    SELECT
        id_produk,
        SUM(jumlah) AS total_terjual,
        SUM(j.jumlah * pr.harga) AS total_pendapatan
    FROM penjualan j
    INNER JOIN produk pr ON j.id_produk = pr.id
    GROUP BY id_produk
) AS ringkasan ON p.id = ringkasan.id_produk
WHERE ringkasan.total_terjual > (
    SELECT AVG(total) FROM (
        SELECT SUM(jumlah) AS total
        FROM penjualan
        GROUP BY id_produk
    ) AS avg_table
)
ORDER BY ringkasan.total_terjual DESC;

Subquery di Klausa SELECT (Scalar Subquery)

Subquery bisa digunakan sebagai kolom dalam SELECT, tetapi harus mengembalikan tepat satu nilai (scalar):

-- Tampilkan setiap produk beserta rata-rata harga kategorinya
SELECT
    nama,
    kategori,
    harga,
    (SELECT ROUND(AVG(harga), 2)
     FROM produk p2
     WHERE p2.kategori = p1.kategori) AS rata_harga_kategori,
    harga - (SELECT ROUND(AVG(harga), 2)
             FROM produk p2
             WHERE p2.kategori = p1.kategori) AS selisih_dari_rata
FROM produk p1
ORDER BY kategori, harga;

Correlated Subquery

Correlated Subquery adalah subquery yang mengacu pada kolom dari outer query. Ia dieksekusi ulang untuk setiap baris dari outer query:

-- Temukan produk yang harganya lebih tinggi dari rata-rata kategorinya sendiri
SELECT nama, kategori, harga
FROM produk p1
WHERE harga > (
    SELECT AVG(harga)
    FROM produk p2
    WHERE p2.kategori = p1.kategori  -- referensi ke outer query
)
ORDER BY kategori, harga DESC;

Subquery dengan EXISTS dan NOT EXISTS

EXISTS memeriksa apakah subquery mengembalikan setidaknya satu baris. Lebih efisien dari IN untuk dataset besar:

-- Cari produk yang pernah terjual menggunakan EXISTS
SELECT id, nama, harga
FROM produk p
WHERE EXISTS (
    SELECT 1 FROM penjualan pj
    WHERE pj.id_produk = p.id
);

-- Cari produk yang belum pernah terjual menggunakan NOT EXISTS
SELECT id, nama, harga
FROM produk p
WHERE NOT EXISTS (
    SELECT 1 FROM penjualan pj
    WHERE pj.id_produk = p.id
);

Subquery vs JOIN: Mana yang Lebih Cepat?

• Untuk sebagian besar kasus di MySQL modern, JOIN cenderung lebih cepat karena optimizer MySQL lebih baik dalam mengoptimalkan JOIN.
• Gunakan EXISTS daripada IN untuk subquery yang mengembalikan banyak baris.
• Gunakan EXPLAIN untuk membandingkan rencana eksekusi query.
• Derived Table di klausa FROM (MySQL 8) sering bisa diganti dengan CTE (WITH) yang lebih mudah dibaca.

Kesimpulan

Subquery adalah alat yang powerful untuk menulis query MySQL yang kompleks. Anda bisa menggunakannya di klausa WHERE (dengan perbandingan tunggal, IN, atau EXISTS), di klausa FROM sebagai derived table, atau di klausa SELECT sebagai scalar subquery. Pahami perbedaan antara regular subquery dan correlated subquery, dan selalu gunakan EXPLAIN untuk memastikan performa query Anda optimal. Dengan menguasai subquery, kemampuan analisis data Anda dengan MySQL akan meningkat drastis.