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 tabel pelanggan, produk, pesanan, dan detail_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 IN mengembalikan NULL, hasilnya akan kosong. Gunakan NOT EXISTS sebagai 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: EXISTS biasanya lebih cepat daripada IN untuk subquery yang besar, karena EXISTS berhenti 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 SELECT bisa lambat untuk tabel besar. Seringkali lebih efisien menggunakan LEFT 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-UPDATE tabel 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 SELECT harus sama.
  • Tipe data kolom yang bersesuaian harus kompatibel.
  • Nama kolom di hasil akhir diambil dari SELECT pertama.
  • ORDER BY hanya boleh diletakkan di akhir, setelah semua UNION.

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 ALL jika 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 EXISTS lebih aman daripada IN / NOT IN saat ada NULL.
  • Derived table di FROM wajib memiliki alias.
  • UNION menghilangkan duplikat (lebih lambat); UNION ALL mempertahankan duplikat (lebih cepat).
  • ORDER BY pada UNION berlaku 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.

Top