Bab sebelumnya menutup dengan satu larangan: derived table tidak boleh merujuk kolom tabel tetangganya, dan pelanggarannya berakhir di ERROR 1054. MySQL 8.0.14 memperkenalkan jalan keluar resmi — kata kunci LATERAL. Sebuah derived table yang diberi tanda LATERAL mendapat izin membaca kolom tabel-tabel yang berdiri di sebelah kirinya di klausa FROM, dan evaluasinya berjalan per baris tabel kiri tersebut. Satu kalimat sederhana itu membuka pola-pola yang sebelumnya mustahil ditulis satu query: ambil tiga transaksi terbesar untuk setiap pelanggan, hitung agregat khusus untuk setiap baris induk, atau panggil subquery parameter-per-baris. Chapter ini membedah pola top-N per grup sampai ke butir eksekusinya, membandingkannya secara jujur dengan pendekatan window function, lalu menguji batas-batas sintaksnya langsung di server.

Menyiapkan Tabel Latihan #

Kita pakai tabel penjualan dengan tujuh transaksi dari lima pelanggan. Komposisi transaksi per pelanggan sengaja dibuat timpang — dua pelanggan punya dua transaksi, tiga lainnya satu — supaya pola top-N dan agregat per induk punya variasi:

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

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 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);

Output yang Dihasilkan:

text 
Query OK, 0 rows affected (0.00 sec)

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

Urutan transaksi terbesar per pelanggan dari data ini: Budi memiliki Teh Melati (3 unit) di atas Kopi Gayo (2 unit); Siti memiliki Kopi Toraja (4 unit) di atas Kopi Gayo (1 unit); sedangkan Andi, Rina, dan Dewi masing-masing hanya punya satu transaksi.

LATERAL: Menghapus Larangan Berkorelasi #

Kita mulai dari kegagalan yang sudah dibuktikan di chapter derived-tables — subquery yang ingin menjumlahkan transaksi milik pelanggan yang sedang dibaca. Tanpa LATERAL, server menolak; dengan LATERAL, query yang sama berjalan. Kedua versinya berdiri berdampingan:

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

Output yang Dihasilkan:

text 
Query OK, 0 rows affected (0.00 sec)

ERROR 1054 (42S22): Unknown column 'p.pelanggan' in 'where clause'
Bye
sql 
SELECT p.id, p.pelanggan, d.total_nilai, d.pesanan
FROM penjualan p
CROSS JOIN LATERAL (
  SELECT SUM(penjualan.jumlah * penjualan.harga) AS total_nilai,
         COUNT(*) AS pesanan
  FROM penjualan
  WHERE penjualan.pelanggan = p.pelanggan
) AS d
ORDER BY p.id;

Output yang Dihasilkan:

text 
+----+-----------+-------------+---------+
| id | pelanggan | total_nilai | pesanan |
+----+-----------+-------------+---------+
|  1 | Budi      |   185000.00 |       2 |
|  2 | Budi      |   185000.00 |       2 |
|  3 | Siti      |   303000.00 |       2 |
|  4 | Siti      |   303000.00 |       2 |
|  5 | Andi      |    90000.00 |       1 |
|  6 | Rina      |    50000.00 |       1 |
|  7 | Dewi      |   310000.00 |       1 |
+----+-----------+-------------+---------+
7 rows in set (0.00 sec)

Versi pertama tetap ditolak ERROR 1054 — rujukan p.pelanggan di dalam subquery tidak dikenal. Versi kedua menyisipkan satu kata LATERAL setelah CROSS JOIN, dan subquery yang sama persis kini sah: kolom p.pelanggan terlihat, jumlahnya dihitung, hasilnya tampil tujuh baris. Bedah mekanismenya: LATERAL mengubah cara derived table dievaluasi — dari “hitung sekali lalu materialisasi” menjadi “jalankan ulang untuk setiap baris tabel kiri”. Baris id 1 (Budi) memicu subquery dengan WHERE pelanggan = 'Budi' yang menjumlahkan dua transaksinya menjadi 185000; baris id 5 (Andi) memicu subquery dengan WHERE pelanggan = 'Andi' hasilnya 90000; demikian tujuh kali. Evaluasi per baris inilah alasan LATERAL sering disebut join dengan subquery berkorelasi — perilaku yang sama dengan subquery berkorelasi di SELECT, tetapi berjalan di klausa FROM dan hasilnya berupa tabel yang bisa di-join.

Perhatikan pula bahwa kolom di dalam subquery LATERAL ditulis terkualifikasi (penjualan.jumlah, penjualan.pelanggan) karena klausa FROM kini memuat dua kemungkinan sumber kolom bernama sama. Menuliskan jumlah tanpa kualifikasi bisa menjadi ambigu antara tabel p dan tabel penjualan di dalam subquery.

Top-N per Grup: Bedah Penuh #

Penggunaan LATERAL paling terkenal adalah top-N per grup: dua transaksi terbesar milik setiap pelanggan. Kuncinya: ORDER BY ... LIMIT di dalam subquery — batas yang selama ini “global” berubah menjadi batas per baris induk:

sql 
SELECT pel.pelanggan, d.produk, d.jumlah
FROM (SELECT DISTINCT pelanggan FROM penjualan) AS pel
CROSS JOIN LATERAL (
  SELECT produk, jumlah
  FROM penjualan
  WHERE penjualan.pelanggan = pel.pelanggan
  ORDER BY jumlah DESC, produk
  LIMIT 2
) AS d
ORDER BY pel.pelanggan, d.jumlah DESC, d.produk;

Output yang Dihasilkan:

text 
+-----------+-------------+--------+
| pelanggan | produk      | jumlah |
+-----------+-------------+--------+
| Andi      | Gula Aren   |      6 |
| Budi      | Teh Melati  |      3 |
| Budi      | Kopi Gayo   |      2 |
| Dewi      | Kopi Toraja |      5 |
| Rina      | Teh Melati  |      2 |
| Siti      | Kopi Toraja |      4 |
| Siti      | Kopi Gayo   |      1 |
+-----------+-------------+--------+
7 rows in set (0.00 sec)

Mari kita bedah baris per baris. Derived table (SELECT DISTINCT pelanggan ...) merapatkan tujuh transaksi menjadi lima baris pelanggan — satu baris induk per pelanggan, karena tanpa DISTINCT, pelanggan dua transaksi akan memanggil subquery LATERAL dua kali dan hasilnya terduplikasi. CROSS JOIN LATERAL (...) kemudian menjalankan subquery untuk tiap pelanggan: WHERE penjualan.pelanggan = pel.pelanggan membatasi ke transaksi si pelanggan, ORDER BY jumlah DESC, produk mengurutkan dari terbesar, dan LIMIT 2 memotong dua teratas. LIMIT inilah bintangnya — pada join biasa LIMIT memotong hasil akhir seluruh query, sedangkan di dalam LATERAL ia memotong hasil subquery per induk: Budi menghasilkan Teh Melati (3) dan Kopi Gayo (2), Siti menghasilkan Kopi Toraja (4) dan Kopi Gayo (1), dan tiga pelanggan bermata satu memunculkan satu-satunya transaksinya. Hasil akhirnya tujuh baris, diurutkan ulang oleh ORDER BY terluar untuk tampilan yang rapi.

Dengan LIMIT 1 pola yang sama menjadi “transaksi terbesar tiap pelanggan”; dengan ORDER BY tanggal DESC ia menjadi “transaksi terakhir tiap pelanggan”; ganti DISTINCT pelanggan menjadi daftar kategori dan polanya menjadi “produk terlaris tiap kategori”.

LATERAL versus Window ROW_NUMBER #

Pendekatan resmi yang sudah kita kuasai untuk top-N per grup adalah ROW_NUMBER() dengan CTE. Kedua pendekatan menghasilkan baris yang sama:

sql 
WITH nomor AS (
  SELECT pelanggan, produk, jumlah,
         ROW_NUMBER() OVER (PARTITION BY pelanggan ORDER BY jumlah DESC, produk) AS rn
  FROM penjualan
)
SELECT pelanggan, produk, jumlah
FROM nomor
WHERE rn <= 2
ORDER BY pelanggan, jumlah DESC, produk;

Output yang Dihasilkan:

text 
+-----------+-------------+--------+
| pelanggan | produk      | jumlah |
+-----------+-------------+--------+
| Andi      | Gula Aren   |      6 |
| Budi      | Teh Melati  |      3 |
| Budi      | Kopi Gayo   |      2 |
| Dewi      | Kopi Toraja |      5 |
| Rina      | Teh Melati  |      2 |
| Siti      | Kopi Toraja |      4 |
| Siti      | Kopi Gayo   |      1 |
+-----------+-------------+--------+
7 rows in set (0.00 sec)

Hasilnya tujuh baris yang identik dengan versi LATERAL. Pilihan di antara keduanya bukan soal benar-salah, melainkan karakter:

AspekCROSS JOIN LATERAL + LIMITROW_NUMBER() + CTE
Cara berpikir“ambil 2 teratas untuk pelanggan ini” — prosedural per grup“nomori semua baris lalu saring” — himpunan
Jumlah baris diproseshanya N teratas per grup setelah urut per grupseluruh baris partisi diberi nomor dulu
Berhenti lebih awalya — LIMIT memotong per gruptidak — rn <= 2 menyaring hasil akhirnya saja
Kolom hasil fleksibelbebas agregat/ekspresi apa pun di subqueryharus lolos lewat satu SELECT jendela
Familiaritas timumum di PostgreSQL, masih asing bagi banyak developer MySQLsudah standar sejak window function populer
Eksekusi per induksubquery dijalankan ulang per baris kirisatu pass dengan sort partisi

Kapan LATERAL unggul? Pertama, saat LIMIT benar-benar berarti berhenti awal: pada tabel jutaan baris dengan index (pelanggan, jumlah), LATERAL ... ORDER BY ... LIMIT 2 mungkin cukup membaca segelintir entry index per pelanggan, sementara versi window mengurutkan seluruh partisi tiap pelanggan sebelum menyaring. Kedua, saat isi subquery butuh lebih dari sekadar memilih kolom — agregat, ekspresi, atau join tambahan di dalam LATERAL tetap terbaca alami. Ketiga, saat N berbeda-beda per grup: LIMIT boleh memakai variabel; rn <= N juga bisa, tetapi jendela harus tetap menghitung semua nomor.

Kapan window function unggul? Saat kita butuh beberapa metrik jendela sekaligus dalam satu query, saat nomor barisnya sendiri ingin ditampilkan, dan saat tim lebih nyaman dengan gaya deklaratifnya. Untuk dataset latihan kita yang kecil, keduanya sama cepat — perbedaan performa baru bicara pada data besar dengan index yang tepat.

Tiga Bentuk Penulisan LATERAL #

LATERAL boleh ditempatkan di tiga bentuk join: setelah koma (gaya lama), setelah CROSS JOIN, dan setelah JOIN ... ON dengan kondisi TRUE:

sql 
SELECT pel.pelanggan, d.produk, d.jumlah
FROM (SELECT DISTINCT pelanggan FROM penjualan) AS pel
JOIN LATERAL (
  SELECT produk, jumlah
  FROM penjualan
  WHERE penjualan.pelanggan = pel.pelanggan
  ORDER BY id
  LIMIT 1
) AS d ON TRUE
ORDER BY pel.pelanggan;

Output yang Dihasilkan:

text 
+-----------+-------------+--------+
| pelanggan | produk      | jumlah |
+-----------+-------------+--------+
| Andi      | Gula Aren   |      6 |
| Budi      | Kopi Gayo   |      2 |
| Dewi      | Kopi Toraja |      5 |
| Rina      | Teh Melati  |      2 |
| Siti      | Kopi Toraja |      4 |
+-----------+-------------+--------+
5 rows in set (0.00 sec)

Bentuk JOIN LATERAL ... ON TRUE menghasilkan lima baris — transaksi pertama (ORDER BY id LIMIT 1) tiap pelanggan. Perbedaan ketiganya ada pada perilaku bila subquery tidak menghasilkan baris: CROSS JOIN LATERAL dan koma menjatuhkan baris induk (inner join), sedangkan LEFT JOIN LATERAL ... ON TRUE mempertahankannya dengan kolom hasil NULL. Untuk “satu ringkasan wajib per pelanggan” pakai CROSS JOIN; untuk “detail opsional — pelanggan tanpa transaksi tetap tampil” pakai LEFT JOIN LATERAL.

Bentuk koma sederhana secara sintaks namun mudah terlewat saat membaca query panjang — CROSS JOIN LATERAL menegaskan niat secara eksplisit dan menjadi gaya yang dianjurkan.

LATERAL untuk Kalkulasi per Induk #

Pola agregat per induk menyusun laporan yang menggabungkan kolom dasar dan kolom ringkasan dalam satu hasil. Kita hitung total nilai dan jumlah pesanan tiap pelanggan — seperti contoh pembuka, tetapi kini induknya daftar pelanggan yang dirapatkan:

sql 
SELECT pel.pelanggan, d.total_nilai, d.pesanan
FROM (SELECT DISTINCT pelanggan FROM penjualan) AS pel
CROSS JOIN LATERAL (
  SELECT SUM(jumlah * harga) AS total_nilai,
         COUNT(*) AS pesanan
  FROM penjualan
  WHERE penjualan.pelanggan = pel.pelanggan
) AS d
ORDER BY d.total_nilai DESC;

Output yang Dihasilkan:

text 
+-----------+-------------+---------+
| pelanggan | total_nilai | pesanan |
+-----------+-------------+---------+
| Dewi      |   310000.00 |       1 |
| Siti      |   303000.00 |       2 |
| Budi      |   185000.00 |       2 |
| Andi      |    90000.00 |       1 |
| Rina      |    50000.00 |       1 |
+-----------+-------------+---------+
5 rows in set (0.00 sec)

Lima baris hasil menyusun papan peringkat: Dewi 310000 dari satu pesanan, Siti 303000 dari dua pesanan, Budi 185000 dari dua, Andi 90000, dan Rina 50000. Versi GROUP BY pelanggan menghasilkan baris yang sama persis untuk kasus ini — agregasi sederhana memang wilayah GROUP BY. LATERAL baru menunjukkan keunggulannya saat ringkasannya tidak seragam: misalnya tiap pelanggan juga ingin membawa “produk favorit” (subquery dengan ORDER BY ... LIMIT 1) atau “pesanan terakhir” di kolom yang sama — subquery-subquery berbeda bentuk itu bisa berdiri berdampingan sebagai beberapa LATERAL atau satu LATERAL multi-kolom, tanpa memaksakan semua metrik ke satu GROUP BY.

Batasan Sintaks yang Dibuktikan #

Izin LATERAL punya batas arah: rujukan hanya boleh menunjuk ke tabel-tabel di sebelah kiri. Membalik arah — subquery LATERAL mencoba membaca tabel yang baru dideklarasikan setelahnya — tetap ditolak:

sql 
-- @error
SELECT d.x
FROM LATERAL (SELECT penjualan.id AS x) AS d, penjualan;

Output yang Dihasilkan:

text 
Query OK, 0 rows affected (0.00 sec)

ERROR 1054 (42S22): Unknown column 'penjualan.id' in 'field list'
Bye

Server menjawab ERROR 1054 — penjualan.id tidak dikenal di posisi itu karena tabel penjualan berdiri di kanan LATERAL, dan saat subquery dievaluasi tabel tersebut belum ada. Aturan penulisan lengkapnya: LATERAL hanya sah di klausa FROM (termasuk di dalam subquery dan CTE), hanya boleh didahului koma, CROSS JOIN, JOIN, atau LEFT JOIN, dan hanya boleh merujuk tabel yang mendahuluinya. Di luar batas itu, LATERAL sangat bebas — subquery-nya boleh memakai ORDER BY, LIMIT, agregat, bahkan LATERAL bersarang lain.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan database latihan kita jatuhkan kembali:

sql 
DROP DATABASE belajar_adv;

Output yang Dihasilkan:

text 
Query OK, 1 row affected (0.00 sec)

Referensi #

LATERAL menyelesaikan peta teknik pembacaan data: derived table mandiri, CTE bernama, window function analitis, dan kini subquery per-baris yang bisa berhenti lebih awal. Semua perangkat itu akan diuji bersama dalam dua chapter penutup section. Pertama, upsert-patterns — teknik menulis data yang aman untuk diputar ulang dan tahan balapan antar-proses; kemudian gap-and-island-problems — teka-teki pengelompokan baris yang memadukan LAG, window agregat, dan CTE bertahap.