Daftar SELECT adalah tempat kita menampilkan kolom dan alias. Ternyata slot di daftar itu juga bisa diisi subquery — sebuah kolom yang nilainya dihitung dari query lain, baris per baris hasil akhir. Laporan “daftar produk beserta berapa kali produk itu terjual” membutuhkan persis bentuk ini: kolom utama berasal dari tabel produk, sementara kolom penghitungnya dirangkum dari tabel transaksi. Pada chapter ini kita membangun kolom kalkulasi semacam itu, menangani produk tanpa transaksi yang menghasilkan NULL, memakai subquery di ORDER BY, lalu jujur menimbang trade-off pola ini: mudah dibaca, tetapi berpotensi mahal di tabel besar.

Menyiapkan Tabel Latihan #

Kita butuh dua tabel: produk sebagai tabel utama dan detail_pesanan sebagai tabel transaksi. Data sama dengan chapter-chapter sebelumnya, sehingga angka-angkanya bisa dicek ulang dengan hitungan manual:

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 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:

text 
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

Sembilan baris detail mencakup empat produk: Kopi Gayo muncul di dua pesanan, Kopi Toraja di dua, Teh Melati di tiga, Gula Aren di dua, sementara Teh Hijau dan Susu Kental tidak muncul sama sekali.

Kolom yang Dihitung per Baris #

Query berikut menampilkan setiap produk berdampingan dengan jumlah pesanan yang memuatnya:

sql 
SELECT p.nama, p.kategori, (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:

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

Enam baris produk tampil, masing-masing dengan nilai jumlah_pesanan yang berbeda-beda: 2, 2, 3, 0, 2, dan 0. Bedah baris-per-barisnya:

  1. (SELECT COUNT(*) FROM detail_pesanan d WHERE d.produk_id = p.id) — subquery menghitung baris detail_pesanan yang produk_id-nya sama dengan p.id, yaitu id produk dari baris luar yang sedang diproses.
  2. Referensi p.id dari dalam subquery — inilah kunci perilakunya: subquery melihat kolom tabel query luar. Ia berjalan sekali per baris hasil, setiap kali dengan nilai p.id yang berbeda, sehingga kolom ini otomatis menjadi subquery berkorelasi (correlated subquery).
  3. AS jumlah_pesanan — alias wajib agar kolom hasil punya nama yang bisa dibaca dan dirujuk di ORDER BY.
  4. COUNT(*) menghasilkan 0 untuk Teh Hijau dan Susu Kental — COUNT terhadap baris yang tidak ada menghasilkan nol, bukan NULL, sehingga produk tanpa transaksi tetap rapi tampil.

Ketika baris luar berganti dari Kopi Gayo ke Kopi Toraja, nilai p.id yang dipakai subquery ikut berganti dari 1 ke 2, dan penghitungan diulang. Enam baris hasil berarti enam kali subquery berjalan — mekanisme per-baris inilah yang membuat pola ini terasa ajaib sekaligus menjadi sumber biaya ekstra yang kita bahas di akhir chapter.

Menangani Baris Tanpa Pasangan #

Menghitung dengan COUNT aman karena hasil terburuknya nol. Agregat lain tidak sebaik itu: SUM atas baris yang tidak ada menghasilkan NULL, seperti sudah kita kenal di chapter fungsi NULL. Query berikut menghitung total unit terjual per produk dengan perlindungan IFNULL:

sql 
SELECT p.nama, (SELECT IFNULL(SUM(d.jumlah), 0) FROM detail_pesanan d WHERE d.produk_id = p.id) AS total_unit FROM produk p ORDER BY p.id;

Output yang Dihasilkan:

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

Kopi Gayo menjual 3 unit (2 + 1), Teh Melati 6 unit (3 + 1 + 2), Gula Aren 5 unit (1 + 4), dan dua produk tanpa transaksi tampil dengan nilai 0 hasil IFNULL — bukan NULL yang meninggalkan lubang kosong di laporan. Perhatikan pula bahwa subquery di kolom ini menjumlahkan jumlah per baris produk; subquery berkorelasi tidak terbatas pada COUNT, semua agregat bisa dipakai selama hasilnya satu nilai per baris luar.

Subquery di ORDER BY #

Pola yang sama bekerja di klausa ORDER BY: pengurutan boleh memakai ekspresi apa pun yang menghasilkan satu nilai per baris, termasuk subquery. Query berikut mengurutkan produk dari yang paling sering dipesan:

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

Output yang Dihasilkan:

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

Teh Melati (3 pesanan) tampil paling atas, disusul tiga produk dengan 2 pesanan yang dipecah urutannya secara alfabetis oleh kunci kedua p.nama, lalu dua produk tanpa transaksi di dasar daftar. Kolom subquery ini bahkan tidak perlu tampil di daftar SELECT — kita mengurutkan berdasarkan nilai yang tidak ditampilkan, teknik yang berguna untuk laporan ringkas.

Keterbacaan versus Performa #

Kolom kalkulasi lewat subquery memang terbaca seperti kalimat: “tampilkan produk, plus hitung berapa kali ia muncul di detail pesanan”. Tetapi mekanisme per-barisnya punya harga. Subquery berkorelasi berjalan sekali untuk setiap baris hasil — laporan 10.000 produk berarti 10.000 kali penghitungan. Untuk tabel kecil seperti latihan kita, biaya itu tidak terasa; untuk tabel produksi, total kerja bisa membengkak.

Query join dengan GROUP BY menghasilkan angka yang sama lewat satu kali penggabungan:

sql 
SELECT p.nama, COUNT(d.pesanan_id) AS jumlah_pesanan
FROM produk p
LEFT JOIN detail_pesanan d ON d.produk_id = p.id
GROUP BY p.id, p.nama
ORDER BY p.nama;

Output yang Dihasilkan:

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

Kolom jumlah_pesanan identik dengan versi subquery: 3 untuk Teh Melati, 2 untuk tiga produk lain, 0 untuk produk tanpa transaksi (hasil LEFT JOIN yang mempertahankan baris produk tanpa pasangan, dihitung COUNT atas kolom detail yang bernilai NULL). Dua catatan teknis pada versi join: COUNT(d.pesanan_id) sengaja menghitung kolom, bukan COUNT(*), agar baris tanpa pasangan terhitung 0; dan GROUP BY p.id, p.nama mengelompokkan berdasarkan kunci utama supaya dua produk yang kebetulan bernama sama tetap terhitung terpisah.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan database latihan kita jatuhkan kembali:

sql 
DROP DATABASE belajar_sub;

Output yang Dihasilkan:

text 
Query OK, 2 rows affected (0.00 sec)

Referensi #

Kolom kalkulasi per baris memperkenalkan kita pada subquery yang melihat tabel luar — correlated subquery — walau baru sepintas. Sebelum membedahnya tuntas, ada satu operator yang justru dibangun di atas pola per-baris ini dan menjadi senjata utama pertanyaan keberadaan: EXISTS. Chapter EXISTS dan NOT EXISTS membahas operator yang berhenti di baris pertama yang ditemukannya.