Derived Tables

Subquery di klausa FROM — derived table — sudah lama menjadi teman kita: pola “agregat dulu di subquery, join hasilnya di query luar” muncul di section subqueries, dan CTE dijelaskan sebagai derived table yang diberi nama. Bab ini membedahnya sampai ke mekanisme di balik layar. Kita akan membuktikan tiga perilaku nyata: mengapa kolom terhitung di dalam derived table bisa difilter di WHERE luar padahal alias di query datar tidak bisa, bagaimana optimizer mengeksekusi derived table (terlihat jelas di EXPLAIN), dan mengapa derived table dilarang merujuk kolom tabel tetangganya — larangan yang justru melahirkan LATERAL di chapter berikutnya. Ditutup dengan perbandingan menyeluruh derived table melawan CTE supaya kita tahu kapan memakai yang mana.

Menyiapkan Tabel Latihan #

Kita pakai tabel transaksi penjualan dengan kolom harga — kombinasi jumlah * harga menjadi bahan kolom terhitung — plus tabel dimensi produk yang menyimpan kategori tiap produk:

DROP DATABASE IF EXISTS belajar_adv;
CREATE DATABASE belajar_adv;
USE belajar_adv;

Output yang Dihasilkan:

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

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE penjualan (
  id INT PRIMARY KEY,
  pelanggan VARCHAR(30) NOT NULL,
  produk VARCHAR(30) NOT NULL,
  jumlah INT NOT NULL,
  harga DECIMAL(10,2) NOT NULL
);

INSERT INTO penjualan (id, pelanggan, produk, jumlah, harga) VALUES
  (1, 'Budi', 'Kopi Gayo',   2, 55000.00),
  (2, 'Budi', 'Teh Melati',  3, 25000.00),
  (3, 'Siti', 'Kopi Toraja', 4, 62000.00),
  (4, 'Siti', 'Kopi Gayo',   1, 55000.00),
  (5, 'Andi', 'Gula Aren',   6, 15000.00),
  (6, 'Rina', 'Teh Melati',  2, 25000.00),
  (7, 'Dewi', 'Kopi Toraja', 5, 62000.00);

CREATE TABLE produk (
  nama VARCHAR(30) PRIMARY KEY,
  kategori VARCHAR(20) NOT NULL
);

INSERT INTO produk (nama, kategori) VALUES
  ('Kopi Gayo',   'Kopi'),
  ('Kopi Toraja', 'Kopi'),
  ('Teh Melati',  'Teh'),
  ('Gula Aren',   'Gula');

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Total nilai per pelanggan dari data ini: Budi 185000, Siti 303000, Andi 90000, Rina 50000, dan Dewi 310000 — angka-angka yang akan berulang di sepanjang contoh. Sebelum lanjut, satu aturan yang tidak berubah: derived table wajib diberi alias (FROM (SELECT ...) AS nama) dan jumlah kolomnya menjadi skema tabel hasil; menulis subquery FROM tanpa alias ditolak dengan error yang sudah kita jumpai di chapter subqueries.

Mengapa Kolom Terhitung Bisa Disaring di WHERE #

Kita langsung ke pertanyaan yang paling sering membingungkan. Di query datar, menyaring alias kolom terhitung di WHERE gagal; di dalam derived table, kolom yang sama persis bisa disaring. Dua eksperimen berikut membuktikan keduanya:

-- @error
SELECT pelanggan, jumlah * harga AS total
FROM penjualan
WHERE total > 100000;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
Bye

SELECT pelanggan, produk, total
FROM (
  SELECT pelanggan, produk, jumlah * harga AS total
  FROM penjualan
) AS d
WHERE total > 100000
ORDER BY total DESC, pelanggan, produk;

Output yang Dihasilkan:

+-----------+-------------+-----------+
| pelanggan | produk      | total     |
+-----------+-------------+-----------+
| Dewi      | Kopi Toraja | 310000.00 |
| Siti      | Kopi Toraja | 248000.00 |
| Budi      | Kopi Gayo   | 110000.00 |
+-----------+-------------+-----------+
3 rows in set (0.00 sec)

Query pertama ditolak ERROR 1054: kolom total tidak dikenal di klausa WHERE. Query kedua berhasil mengeluarkan tiga transaksi di atas seratus ribu — Dewi (310000), Siti (248000), Budi (110000) — padahal kolom total dihitung dari ekspresi yang sama. Perbedaannya ada pada urutan eksekusi logis. SELECT dan WHERE query datar diproses dengan WHERE lebih dulu, jadi ketika WHERE dievaluasi, alias total belum ada. Derived table membalik situasi: subquery di FROM dieksekusi dan diselesaikan lebih dulu, menghasilkan tabel hasil yang kolomnya — termasuk total — sudah berupa kolom sungguhan; ketika giliran WHERE query luar tiba, kolom itu sudah ada dan bisa disaring seperti kolom tabel biasa.

Pola ini adalah alasan klasik orang membungkus query dengan derived table: bukan untuk mengubah hasil, melainkan untuk mengangkat ekspresi menjadi kolom yang bisa disaring, diurutkan, atau di-join. HAVING memang bisa menyaring agregat, tetapi HAVING tidak membantu untuk ekspresi non-agregat seperti jumlah * harga.

Derived Table Bersarang: Filter di Atas Rata-rata #

Derived table boleh berisi derived table lain — bersarang dalam tanda kurung. Kasus klasiknya: pelanggan yang total belanjanya di atas rata-rata total seluruh pelanggan. Rata-rata itu sendiri dihitung dari hasil agregasi, sehingga kita butuh dua lapis dan subquery skalar:

SELECT pelanggan, total_nilai
FROM (
  SELECT pelanggan, SUM(jumlah * harga) AS total_nilai
  FROM penjualan
  GROUP BY pelanggan
) AS rekap
WHERE total_nilai > (
  SELECT AVG(total_nilai)
  FROM (
    SELECT SUM(jumlah * harga) AS total_nilai
    FROM penjualan
    GROUP BY pelanggan
  ) AS rataan
)
ORDER BY total_nilai DESC;

Output yang Dihasilkan:

+-----------+-------------+
| pelanggan | total_nilai |
+-----------+-------------+
| Dewi      |   310000.00 |
| Siti      |   303000.00 |
+-----------+-------------+
2 rows in set (0.00 sec)

Derived table rekap meringkas tujuh transaksi menjadi lima baris pelanggan. Subquery skalar di WHERE menghitung rata-rata dari derived table kedua bernama rataan yang definisinya identik dengan rekap — rata-ratanya 187600. Filter total_nilai > 187600 menyisakan dua baris: Dewi (310000) dan Siti (303000). Perhatikan kelemahan yang sudah Anda duga: agregasi SUM(jumlah * harga) GROUP BY pelanggan harus ditulis dua kali karena derived table tidak bisa dipanggil dua kali. Satu kesalahan ketik di salah satu salinan membuat kedua angka tidak sinkron — persis masalah yang diselesaikan CTE dengan satu definisi bernama, dan kita bandingkan keduanya di bagian akhir chapter.

Derived Table Tidak Boleh Berkorelasi #

Batasan fundamental derived table: definisinya tidak boleh merujuk kolom tabel lain di klausa FROM yang sama. Kita buktikan dengan mencoba menghitung total belanja tiap pelanggan sebagai derived table yang menyaring pelanggan dari tabel tetangga:

-- @error
SELECT p.pelanggan, d.total_nilai
FROM penjualan p,
     (
       SELECT SUM(jumlah * harga) AS total_nilai
       FROM penjualan
       WHERE penjualan.pelanggan = p.pelanggan
     ) AS d;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1054 (42S22): Unknown column 'p.pelanggan' in 'where clause'
Bye

Server menolak dengan ERROR 1054 — kolom p.pelanggan tidak dikenal dari dalam subquery. Pada saat derived table diproses, ia berdiri sendiri: hanya tabel-tabel yang disebut di dalam definisinya sendiri yang terlihat, alias p milik query luar berada di luar jangkauan. Inilah sebabnya pola agregat-per-pelanggan selama ini kita tulis dengan GROUP BY di derived table lalu join hasilnya — bukan dengan filter berkorelasi. Batasan ini memang sengaja: menjaga derived table sebagai unit mandiri yang mudah dioptimasi. Bab berikutnya memperkenalkan kata kunci LATERAL yang melonggarkan aturan ini secara terkendali — derived table boleh merujuk kolom tabel di kirinya, satu per baris.

Derived Table atau CTE? #

CTE secara semantik adalah derived table yang diberi nama di depan statement — optimizer MySQL bahkan memperlakukan keduanya dengan jalur eksekusi yang sama. Versi CTE dari query rata-rata di atas memindahkan definisi ke klausa WITH dan menyebutnya dua kali:

WITH rekap AS (
  SELECT pelanggan, SUM(jumlah * harga) AS total_nilai
  FROM penjualan
  GROUP BY pelanggan
)
SELECT pelanggan, total_nilai
FROM rekap
WHERE total_nilai > (SELECT AVG(total_nilai) FROM rekap)
ORDER BY total_nilai DESC;

Output yang Dihasilkan:

+-----------+-------------+
| pelanggan | total_nilai |
+-----------+-------------+
| Dewi      |   310000.00 |
| Siti      |   303000.00 |
+-----------+-------------+
2 rows in set (0.00 sec)

Hasilnya dua baris yang identik — Dewi dan Siti — dengan agregasi ditulis satu kali. Perbandingan menyeluruhnya sebagai berikut:

Pilihan praktisnya mengikuti panjang query. Subquery satu lapis yang dipakai sekali — misalnya menyaring kolom terhitung — justru lebih ringkas ditulis langsung sebagai derived table. Begitu definisi yang sama dibutuhkan di dua tempat atau tahapan mulai bertumpuk, CTE menang telak: satu sumber kebenaran dan struktur baca dari atas ke bawah.

Materialisasi: Melihat Eksekusi Lewat EXPLAIN #

Bagaimana server benar-benar menjalankan derived table? EXPLAIN menjawabnya dengan dua penanda khas: select_type DERIVED pada baris subquery dan tabel <derived2> di baris query luar:

EXPLAIN
SELECT d.pelanggan, d.total_nilai
FROM (
  SELECT pelanggan, SUM(jumlah * harga) AS total_nilai
  FROM penjualan
  GROUP BY pelanggan
) AS d
WHERE d.total_nilai > 100000;

Output yang Dihasilkan:

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | NULL            |
|  2 | DERIVED     | penjualan  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in set, 1 warning (0.00 sec)

Dua baris rencana eksekusi terbaca dari bawah ke atas. Baris kedua ber-label DERIVED adalah subquery: server membaca seluruh penjualan, mengelompokkan per pelanggan (Using temporary), dan menyimpan hasilnya sebagai tabel sementara — proses ini disebut materialisasi. Baris pertama kemudian membaca tabel material itu (ditulis <derived2>, merujuk hasil tahap ke-2) dan menerapkan filter total_nilai > 100000. Angka pada kolom rows menunjukkan estimasi baris yang dibaca di tiap tahap.

Materialisasi tidak selalu terjadi. Untuk derived table sederhana tanpa GROUP BY atau agregat, optimizer MySQL umumnya melebur (merge) subquery ke dalam query luar sehingga tidak ada tabel sementara yang dibuat — saklar optimizer derived_merge mengendalikan perilaku ini. Derived table beragregat seperti contoh kita hampir selalu dimaterialisasi karena hasil pengelompokannya harus utuh sebelum bisa difilter. Konsekuensinya penting untuk tabel besar: materialisasi membaca seluruh sumber sekali di tahap awal, jadi derived table yang memfilter sedikit baris dari tabel raksasa tetap membaca semuanya lebih dulu.

Inline View: Derived Table yang Di-join #

Istilah lama yang masih populer untuk derived table adalah inline view — “view yang ditulis langsung di dalam query”. Nama itu pas untuk pola terakhir kita: derived table per produk di-join dengan tabel dimensi produk untuk melaporkan total per kategori:

SELECT pr.kategori,
       SUM(d.total_nilai) AS total_kategori
FROM (
  SELECT produk, SUM(jumlah * harga) AS total_nilai
  FROM penjualan
  GROUP BY produk
) AS d
JOIN produk pr ON pr.nama = d.produk
GROUP BY pr.kategori
ORDER BY total_kategori DESC;

Output yang Dihasilkan:

+----------+----------------+
| kategori | total_kategori |
+----------+----------------+
| Kopi     |      723000.00 |
| Teh      |      125000.00 |
| Gula     |       90000.00 |
+----------+----------------+
3 rows in set (0.00 sec)

Derived table d meringkas penjualan menjadi empat baris produk — Kopi Gayo 165000, Kopi Toraja 558000, Teh Melati 125000, Gula Aren 90000. JOIN produk melampirkan kolom kategori ke tiap baris, lalu GROUP BY kategori di query luar menjumlahkan ulang menjadi Kopi 723000, Teh 125000, Gula 90000. Inilah pola star-join sederhana: fakta diringkas dulu di inline view, baru digabung dengan dimensi. Semua produk penjualan ada di tabel produk, sehingga JOIN dalam tidak menjatuhkan baris; bila ada produk baru di transaksi yang belum terdaftar di dimensi, LEFT JOIN menjaga agar barisnya tetap muncul dengan kategori NULL.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan database latihan kita jatuhkan kembali:

DROP DATABASE belajar_adv;

Output yang Dihasilkan:

Query OK, 2 rows affected (0.00 sec)

Referensi #

• Derived Tables — MySQL 8.4 Reference Manual
• Optimizing Derived Tables — MySQL 8.4 Reference Manual
• SELECT Statement — MySQL 8.4 Reference Manual

Derived table kini kita pahami sampai ke urutan eksekusi dan materialisasinya: kolom terhitung bisa disaring karena subquery FROM tuntas lebih dulu, EXPLAIN memperlihatkan tahap DERIVED yang hasilnya dibaca ulang oleh query luar, dan upaya berkorelasi ditolak tegas. Larangan terakhir itulah yang dibongkar chapter berikutnya: LATERAL memberi izin terbatas bagi derived table untuk membaca kolom tabel di sebelah kirinya — izin yang membuka pola top-N per grup paling mudah dibaca di seluruh SQL.