Subquery & Union
Subquery memungkinkan query bersarang di dalam query lain, sementara UNION menggabungkan hasil dari beberapa query. Keduanya adalah teknik lanjutan yang sangat berguna untuk analisis data kompleks.
Pendahuluan
Pada artikel JOIN, kita menggabungkan tabel secara horizontal (menambah kolom). Namun ada kalanya kita perlu:
- Menggunakan hasil satu query sebagai input bagi query lain (subquery).
- Menggabungkan hasil beberapa query secara vertikal (UNION).
Kedua teknik ini memperluas kemampuan SQL Anda dan sering muncul dalam analisis data maupun pengembangan aplikasi.
Asumsi: Anda berada di database
toko_online(USE toko_online;) dengan data contoh pada tabelpelanggan,produk,pesanan, dandetail_pesanan.
Subquery: Query di Dalam Query
Subquery (atau inner query / nested query) adalah query SELECT yang dieksekusi di dalam query lain. Subquery selalu diapit tanda kurung.
Klasifikasi Subquery
Berdasarkan hasil yang dikembalikan:
- Scalar subquery — mengembalikan satu nilai (satu baris, satu kolom).
- Row subquery — mengembalikan satu baris dengan beberapa kolom.
- Column subquery — mengembalikan satu kolom dengan banyak baris.
- Table subquery — mengembalikan tabel (banyak baris, banyak kolom).
Berdasarkan posisi:
- Di klausa
SELECT. - Di klausa
WHERE/HAVING. - Di klausa
FROM. - Di klausa
INSERT,UPDATE,DELETE.
Subquery di Klausa WHERE
Ini penggunaan subquery yang paling umum.
1. Subquery dengan Operator Perbandingan (Scalar Subquery)
SELECT nama, harga
FROM produk
WHERE harga > (SELECT AVG(harga) FROM produk);
Subquery (SELECT AVG(harga) FROM produk) mengembalikan satu nilai (rata-rata harga), lalu WHERE membandingkan setiap baris dengan nilai itu.
Hasilnya: semua produk yang harganya di atas rata-rata.
2. Subquery dengan IN / NOT IN
SELECT id, nama, email
FROM pelanggan
WHERE id IN (
SELECT pelanggan_id FROM pesanan WHERE total > 1000000
);
Subquery mengembalikan daftar pelanggan_id yang memiliki pesanan > 1.000.000; query luar menampilkan detail pelanggan tersebut.
NOT IN:
SELECT id, nama
FROM pelanggan
WHERE id NOT IN (
SELECT pelanggan_id FROM pesanan
);
Pelanggan yang belum pernah memesan.
⚠️ Peringatan: Jika subquery
NOT INmengembalikanNULL, hasilnya akan kosong. GunakanNOT EXISTSsebagai alternatif yang lebih aman.
3. Subquery dengan EXISTS / NOT EXISTS
EXISTS mengembalikan TRUE jika subquery menghasilkan setidaknya satu baris. Subquery di sini biasanya berkorelasi dengan query luar.
EXISTS:
SELECT p.id, p.nama
FROM pelanggan AS p
WHERE EXISTS (
SELECT 1 FROM pesanan AS ps
WHERE ps.pelanggan_id = p.id AND ps.total > 1000000
);
Pelanggan yang memiliki setidaknya satu pesanan > 1.000.000.
NOT EXISTS:
SELECT p.id, p.nama
FROM pelanggan AS p
WHERE NOT EXISTS (
SELECT 1 FROM pesanan AS ps
WHERE ps.pelanggan_id = p.id
);
Pelanggan yang belum pernah memesan — sama dengan NOT IN tetapi lebih aman terhadap NULL.
Tips:
EXISTSbiasanya lebih cepat daripadaINuntuk subquery yang besar, karenaEXISTSberhenti begitu menemukan satu baris yang cocok.
4. Subquery dengan ANY, SOME, ALL
ANY / SOME — kondisi terpenuhi jika setidaknya satu nilai dari subquery memenuhi perbandingan:
SELECT nama, harga
FROM produk
WHERE harga > ANY (
SELECT harga FROM produk WHERE stok = 0
);
Menampilkan produk yang harganya lebih besar dari minimal satu produk yang stoknya habis. Setara dengan > (SELECT MIN(harga) FROM produk WHERE stok = 0).
ALL — kondisi terpenuhi jika semua nilai dari subquery memenuhi perbandingan:
SELECT nama, harga
FROM produk
WHERE harga > ALL (
SELECT harga FROM produk WHERE stok = 0
);
Menampilkan produk yang harganya lebih besar dari semua produk yang stoknya habis. Setara dengan > (SELECT MAX(harga) FROM produk WHERE stok = 0).
5. Subquery Berkorelasi (Correlated Subquery)
Subquery berkorelasi jika ia merujuk ke kolom dari query luar. Subquery ini dieksekusi ulang untuk setiap baris query luar, sehingga bisa lebih lambat.
SELECT
p.id,
p.nama,
(SELECT COUNT(*) FROM pesanan AS ps WHERE ps.pelanggan_id = p.id) AS jumlah_pesanan
FROM pelanggan AS p;
Untuk setiap pelanggan, subquery menghitung jumlah pesanannya.
Contoh lain: produk di atas rata-rata per kategori stok
SELECT pr.nama, pr.harga, pr.stok
FROM produk AS pr
WHERE pr.harga > (
SELECT AVG(p2.harga)
FROM produk AS p2
WHERE (p2.stok = 0) = (pr.stok = 0)
);
Subquery merujuk pr.stok dari query luar, sehingga dieksekusi per baris.
Subquery di Klausa SELECT
Subquery di SELECT harus berupa scalar subquery (mengembalikan satu nilai per baris).
SELECT
p.id,
p.nama,
(SELECT COUNT(*) FROM pesanan AS ps WHERE ps.pelanggan_id = p.id) AS jumlah_pesanan,
(SELECT IFNULL(SUM(total), 0) FROM pesanan AS ps WHERE ps.pelanggan_id = p.id) AS total_belanja
FROM pelanggan AS p
ORDER BY total_belanja DESC;
Setiap pelanggan menampilkan jumlah pesanan dan total belanjanya.
Tips: Subquery berkorelasi di
SELECTbisa lambat untuk tabel besar. Seringkali lebih efisien menggunakanLEFT JOIN+GROUP BY.
Alternatif dengan LEFT JOIN:
SELECT
p.id,
p.nama,
COUNT(ps.id) AS jumlah_pesanan,
IFNULL(SUM(ps.total), 0) AS total_belanja
FROM pelanggan AS p
LEFT JOIN pesanan AS ps ON ps.pelanggan_id = p.id
GROUP BY p.id, p.nama
ORDER BY total_belanja DESC;
Subquery di Klausa FROM (Derived Table)
Subquery di FROM disebut derived table — hasilnya diperlakukan sebagai tabel sementara.
Sintaks
SELECT kolom
FROM (
SELECT ... FROM ...
) AS alias_subquery;
Alias wajib diberikan pada derived table.
Contoh: Rata-rata Total Belanja per Pelanggan
SELECT
ROUND(AVG(total_belanja), 2) AS rata_rata_belanja
FROM (
SELECT pelanggan_id, SUM(total) AS total_belanja
FROM pesanan
GROUP BY pelanggan_id
) AS ringkasan;
Subquery menghitung total belanja per pelanggan; query luar menghitung rata-ratanya.
Contoh: Filter Hasil Agregasi
SELECT *
FROM (
SELECT
produk_id,
SUM(jumlah) AS total_terjual
FROM detail_pesanan
GROUP BY produk_id
) AS penjualan
WHERE total_terjual > 10
ORDER BY total_terjual DESC;
Derived table memungkinkan kita memfilter hasil agregasi tanpa HAVING (meskipun HAVING juga bisa digunakan).
Contoh: JOIN dengan Derived Table
SELECT
pr.nama,
penjualan.total_terjual
FROM produk AS pr
INNER JOIN (
SELECT produk_id, SUM(jumlah) AS total_terjual
FROM detail_pesanan
GROUP BY produk_id
) AS penjualan ON penjualan.produk_id = pr.id
ORDER BY penjualan.total_terjual DESC;
Subquery di INSERT, UPDATE, dan DELETE
INSERT ... SELECT
Menyalin data dari satu tabel ke tabel lain:
CREATE TABLE pelanggan_vip (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
pelanggan_id INT UNSIGNED NOT NULL,
total_belanja DECIMAL(12,2) NOT NULL
);
INSERT INTO pelanggan_vip (pelanggan_id, total_belanja)
SELECT pelanggan_id, SUM(total)
FROM pesanan
GROUP BY pelanggan_id
HAVING SUM(total) > 1000000;
UPDATE dengan Subquery
UPDATE pesanan
SET total = (
SELECT SUM(harga_satuan * jumlah)
FROM detail_pesanan
WHERE detail_pesanan.pesanan_id = pesanan.id
)
WHERE id = 1;
⚠️ Catatan MySQL: Anda tidak bisa meng-
UPDATEtabel yang sama yang sedang dibaca oleh subquery secara langsung. Gunakan derived table sebagai perantara:UPDATE pesanan SET total = ( SELECT total_baru FROM ( SELECT pesanan_id, SUM(harga_satuan * jumlah) AS total_baru FROM detail_pesanan GROUP BY pesanan_id ) AS sub WHERE sub.pesanan_id = pesanan.id ) WHERE id = 1;
DELETE dengan Subquery
DELETE FROM pelanggan
WHERE id NOT IN (
SELECT pelanggan_id FROM pesanan
);
Menghapus pelanggan yang belum pernah memesan.
UNION dan UNION ALL
UNION menggabungkan hasil dari dua atau lebih query SELECT secara vertikal (menambah baris).
Aturan UNION
- Jumlah kolom di setiap
SELECTharus sama. - Tipe data kolom yang bersesuaian harus kompatibel.
- Nama kolom di hasil akhir diambil dari
SELECTpertama. ORDER BYhanya boleh diletakkan di akhir, setelah semuaUNION.
Sintaks
SELECT kolom FROM tabel_a
UNION
SELECT kolom FROM tabel_b;
UNION vs UNION ALL
| Aspek | UNION |
UNION ALL |
|---|---|---|
| Duplikat | Dihilangkan | Dipertahankan |
| Performa | Lebih lambat (perlu sorting/hashing) | Lebih cepat |
| Kapan dipakai | Butuh hasil unik | Butuh semua baris, termasuk duplikat |
Tips: Gunakan
UNION ALLjika Anda yakin tidak ada duplikat atau memang ingin mempertahankannya — performanya lebih baik.
Contoh 1: Menggabungkan Dua Query
SELECT nama, 'pelanggan' AS tipe FROM pelanggan
UNION
SELECT nama, 'produk' AS tipe FROM produk
ORDER BY nama;
Menggabungkan nama pelanggan dan nama produk dalam satu daftar, dengan kolom tipe sebagai penanda.
Contoh 2: UNION ALL
SELECT status FROM pesanan WHERE total > 500000
UNION ALL
SELECT status FROM pesanan WHERE total <= 500000;
Menampilkan semua status tanpa menghilangkan duplikat.
Contoh 3: Simulasi FULL JOIN
Seperti dibahas di artikel JOIN, MySQL tidak mendukung FULL JOIN. Simulasikan dengan LEFT JOIN + UNION + RIGHT JOIN:
SELECT p.id, p.nama, ps.id AS pesanan_id
FROM pelanggan AS p
LEFT JOIN pesanan AS ps ON ps.pelanggan_id = p.id
UNION
SELECT p.id, p.nama, ps.id AS pesanan_id
FROM pelanggan AS p
RIGHT JOIN pesanan AS ps ON ps.pelanggan_id = p.id;
Contoh 4: Laporan Multi-Sumber
Misalkan kita ingin laporan gabungan dari tabel pesanan (penjualan) dan tabel pengeluaran (biaya):
SELECT
'penjualan' AS jenis,
DATE(dibuat_pada) AS tanggal,
SUM(total) AS jumlah
FROM pesanan
WHERE status = 'selesai'
GROUP BY DATE(dibuat_pada)
UNION ALL
SELECT
'pengeluaran' AS jenis,
DATE(tanggal) AS tanggal,
SUM(jumlah) AS jumlah
FROM pengeluaran
GROUP BY DATE(tanggal)
ORDER BY tanggal, jenis;
UNION dengan ORDER BY dan LIMIT
ORDER BY dan LIMIT di akhir berlaku untuk seluruh hasil UNION:
(SELECT nama, harga FROM produk WHERE stok > 0)
UNION ALL
(SELECT nama, harga FROM produk WHERE stok = 0)
ORDER BY harga DESC
LIMIT 10;
Jika ingin ORDER BY / LIMIT hanya untuk salah satu SELECT, bungkus dengan tanda kurung:
(SELECT nama, harga FROM produk ORDER BY harga DESC LIMIT 5)
UNION ALL
(SELECT nama, harga FROM produk ORDER BY harga ASC LIMIT 5);
Perbandingan Subquery vs JOIN
Banyak masalah bisa diselesaikan dengan subquery atau JOIN. Berikut panduan memilih:
| Aspek | Subquery | JOIN |
|---|---|---|
| Keterbacaan | Lebih jelas untuk logika bertingkat | Lebih jelas untuk relasi antar tabel |
| Performa | Bisa lambat jika berkorelasi | Umumnya lebih cepat untuk relasi |
| Fleksibilitas | Bisa mengembalikan scalar, list, atau tabel | Hanya menggabungkan baris |
| Kapan dipakai | Butuh nilai agregat, EXISTS, IN |
Butuh kolom dari beberapa tabel |
Contoh: dua cara mendapatkan pelanggan yang punya pesanan
Subquery:
SELECT * FROM pelanggan
WHERE id IN (SELECT pelanggan_id FROM pesanan);
JOIN:
SELECT DISTINCT p.*
FROM pelanggan AS p
INNER JOIN pesanan AS ps ON ps.pelanggan_id = p.id;
Keduanya menghasilkan hal yang sama; pilih yang lebih mudah dibaca dan dioptimasi.
Praktik: Query Kompleks dengan Subquery & UNION
1. Produk yang Harganya di Atas Rata-rata Kategori Stoknya
SELECT pr.nama, pr.harga, pr.stok
FROM produk AS pr
WHERE pr.harga > (
SELECT AVG(p2.harga)
FROM produk AS p2
WHERE (p2.stok = 0) = (pr.stok = 0)
);
2. Pelanggan dengan Total Belanja di Atas Rata-rata
SELECT pelanggan_id, total_belanja
FROM (
SELECT pelanggan_id, SUM(total) AS total_belanja
FROM pesanan
GROUP BY pelanggan_id
) AS ringkasan
WHERE total_belanja > (
SELECT AVG(total_belanja)
FROM (
SELECT SUM(total) AS total_belanja
FROM pesanan
GROUP BY pelanggan_id
) AS rata
);
3. Laporan Gabungan Pelanggan dan Produk
SELECT 'pelanggan' AS entitas, nama, email AS keterangan FROM pelanggan
UNION ALL
SELECT 'produk' AS entitas, nama, CONCAT('Rp', FORMAT(harga, 0)) AS keterangan FROM produk
ORDER BY entitas, nama;
4. Produk yang Belum Pernah Terjual
SELECT pr.id, pr.nama
FROM produk AS pr
WHERE NOT EXISTS (
SELECT 1 FROM detail_pesanan AS dp WHERE dp.produk_id = pr.id
);
Ringkasan
| Teknik | Kegunaan |
|---|---|
Subquery di WHERE |
Membandingkan dengan hasil query lain (IN, EXISTS, ANY, ALL) |
Subquery di SELECT |
Menampilkan nilai agregat per baris (scalar subquery) |
Subquery di FROM |
Derived table — tabel sementara dari hasil query |
| Subquery berkorelasi | Subquery yang merujuk kolom query luar, dieksekusi per baris |
UNION |
Menggabungkan hasil query, menghilangkan duplikat |
UNION ALL |
Menggabungkan hasil query, mempertahankan duplikat |
Poin penting:
- Subquery selalu diapit tanda kurung.
EXISTS/NOT EXISTSlebih aman daripadaIN/NOT INsaat adaNULL.- Derived table di
FROMwajib memiliki alias. UNIONmenghilangkan duplikat (lebih lambat);UNION ALLmempertahankan duplikat (lebih cepat).ORDER BYpadaUNIONberlaku untuk seluruh hasil, diletakkan di akhir.
Pada artikel berikutnya, kita akan membahas View, Index, dan Stored Procedure — objek-objek database yang meningkatkan efisiensi dan maintainability.