Chapter LATERAL Join di section JOIN sudah memperkenalkan kata kuncinya dan pola andalannya: top-N per kelompok lewat LEFT JOIN LATERAL (...) LIMIT 1. Bagian lanjutan ini memakai LATERAL untuk beban kerja yang lebih berat: agregat yang dihitung hanya dari beberapa event terakhir tiap pengguna, deret dan baris yang jumlahnya berbeda untuk setiap baris luar, perbandingan langsung dengan correlated subquery beserta batas kerasnya, ledakan array yang mempertahankan posisi, dan rantai LATERAL bertingkat sebagai pipeline transformasi. Model mental yang dipakai di seluruh chapter: FROM pengguna adalah loop luar, dan subquery LATERAL adalah isi loop yang dievaluasi sekali per baris — database menjalankan “for-each” atas nama Anda.

Dataset: Pengguna dan Log Aktivitas #

Dua tabel mengikuti domain konsisten section ini: pengguna dan aktivitas (log event ber-timestamp tetap, Januari 2026). Kolom poin menyimpan skor engagement tiap event. Andi Wijaya sengaja tidak punya satu pun aktivitas — baris itu terus menguji pola LEFT JOIN LATERAL sepanjang chapter, dan kolom minat berupa array yang dipakai pada bagian ledakan array:

sql buat-tabel.sql 
-- Tabel latihan chapter Lateral Join: pengguna dan log aktivitasnya.
-- Andi Wijaya sengaja tanpa aktivitas apa pun (uji LEFT JOIN LATERAL).
CREATE TABLE pengguna (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    minat text[] NOT NULL DEFAULT '{}'
);

CREATE TABLE aktivitas (
    id           integer PRIMARY KEY,
    pengguna_id  integer NOT NULL REFERENCES pengguna (id),
    waktu        timestamp NOT NULL,
    jenis        text NOT NULL,
    poin         integer NOT NULL
);

INSERT INTO pengguna (id, nama, minat) VALUES
    (1, 'Budi Santoso', ARRAY['kopi', 'sql']),
    (2, 'Siti Rahayu',  ARRAY['sql', 'bi', 'kopi']),
    (3, 'Andi Wijaya',  ARRAY['jaringan']),
    (4, 'Dewi Lestari', ARRAY['bi', 'cloud']);

INSERT INTO aktivitas (id, pengguna_id, waktu, jenis, poin) VALUES
    (101, 1, TIMESTAMP '2026-01-04 08:00:00', 'daftar',   50),
    (102, 1, TIMESTAMP '2026-01-05 08:00:00', 'login',    10),
    (103, 1, TIMESTAMP '2026-01-05 09:30:00', 'beli',    500),
    (104, 1, TIMESTAMP '2026-01-08 10:00:00', 'login',    10),
    (105, 2, TIMESTAMP '2026-01-04 08:30:00', 'daftar',   50),
    (106, 2, TIMESTAMP '2026-01-06 09:00:00', 'beli',    300),
    (107, 2, TIMESTAMP '2026-01-10 14:00:00', 'login',    25),
    (108, 2, TIMESTAMP '2026-01-12 11:00:00', 'beli',    700),
    (109, 4, TIMESTAMP '2026-01-05 20:00:00', 'daftar',   50),
    (110, 4, TIMESTAMP '2026-01-06 20:00:00', 'login',    12),
    (111, 4, TIMESTAMP '2026-01-07 21:00:00', 'beli',    200);

SELECT id, nama, minat FROM pengguna ORDER BY id;

SELECT id, pengguna_id, waktu, jenis, poin
FROM aktivitas
ORDER BY id;

Output yang Dihasilkan:

text 
CREATE TABLE
CREATE TABLE
INSERT 0 4
INSERT 0 11
 id |     nama     |     minat     
----+--------------+---------------
  1 | Budi Santoso | {kopi,sql}
  2 | Siti Rahayu  | {sql,bi,kopi}
  3 | Andi Wijaya  | {jaringan}
  4 | Dewi Lestari | {bi,cloud}
(4 rows)

 id  | pengguna_id |        waktu        | jenis  | poin 
-----+-------------+---------------------+--------+------
 101 |           1 | 2026-01-04 08:00:00 | daftar |   50
 102 |           1 | 2026-01-05 08:00:00 | login  |   10
 103 |           1 | 2026-01-05 09:30:00 | beli   |  500
 104 |           1 | 2026-01-08 10:00:00 | login  |   10
 105 |           2 | 2026-01-04 08:30:00 | daftar |   50
 106 |           2 | 2026-01-06 09:00:00 | beli   |  300
 107 |           2 | 2026-01-10 14:00:00 | login  |   25
 108 |           2 | 2026-01-12 11:00:00 | beli   |  700
 109 |           4 | 2026-01-05 20:00:00 | daftar |   50
 110 |           4 | 2026-01-06 20:00:00 | login  |   12
 111 |           4 | 2026-01-07 21:00:00 | beli   |  200
(11 rows)

Agregat per Baris: N Event Terakhir #

Pertanyaan pertama sulit bagi GROUP BY biasa: “berapa total poin dari tiga aktivitas terakhir tiap pengguna?”. GROUP BY mengagregasi seluruh riwayat; LIMIT tidak bisa berlaku per pengguna di query datar. LATERAL menyelesaikannya dengan subquery dua lapis: lapis dalam memilih tiga baris terbaru untuk p.id tertentu, lapis luar mengagregasi ketiganya:

sql agregat-per-baris.sql 
-- LATERAL dengan agregat per baris: total poin 3 aktivitas TERAKHIR tiap pengguna.
-- Subquery di dalam LATERAL melihat p.id dari baris luar; LIMIT 3 berlaku
-- per pengguna -- sesuatu yang mustahil bagi subquery FROM biasa.
SELECT p.nama,
       tiga.jumlah_event,
       tiga.total_poin
FROM pengguna p
LEFT JOIN LATERAL (
    SELECT count(*) AS jumlah_event,
           coalesce(sum(poin), 0) AS total_poin
    FROM (
        SELECT a.poin
        FROM aktivitas a
        WHERE a.pengguna_id = p.id
        ORDER BY a.waktu DESC
        LIMIT 3
    ) tiga_terakhir
) tiga ON true
ORDER BY p.id;

Output yang Dihasilkan:

text 
     nama     | jumlah_event | total_poin 
--------------+--------------+------------
 Budi Santoso |            3 |        520
 Siti Rahayu  |            3 |       1025
 Andi Wijaya  |            0 |          0
 Dewi Lestari |            3 |        262
(4 rows)

Mari kita bedah tiga lapisnya untuk Siti Rahayu:

  1. Baris luar pengguna p menyerahkan p.id = 2 ke subquery LATERAL.
  2. Lapis dalam tiga_terakhir menyaring pengguna_id = 2, mengurutkan menurun, lalu mengambil LIMIT 3 — tiga event terakhir Siti: beli 700, login 25, beli 300.
  3. Lapis luar menjumlah 700 + 25 + 300 = 1025 dan mengembalikan satu baris agregat; LEFT JOIN ... ON true menjaga pengguna tanpa aktivitas (Andi: jumlah_event 0, total_poin 0) tetap tampil.

Pola “pilih N dulu, agregatkan belakangan” ini juga menjadi fondasi rolling metric yang lebih rumit — rata-rata bergerak per pengguna cukup mengganti sum menjadi avg.

LATERAL sebagai Loop Implisit #

Dua contoh berikut memperlihatkan model mental “for-each” secara harfiah. Query pertama: untuk setiap pengguna, bangkitkan deret tanggal dari event pertamanya sampai event terakhirnya — generate_series menerima batas yang berbeda untuk setiap baris luar, sehingga panjang hasil per pengguna berbeda-beda (Budi 5 hari, Siti 9 hari, Dewi 3 hari). Query kedua mengembangkan setiap event daftar menjadi dua baris pengingat yang waktunya dihitung dari a.waktu baris luar:

sql loop-implisit.sql 
-- LATERAL sebagai loop implisit: satu definisi dievaluasi sekali per baris luar.

-- (1) generate_series dengan BATAS PER BARIS: deret tanggal dari event pertama
-- sampai event terakhir tiap pengguna. Panjang deret berbeda-beda per pengguna.
SELECT p.nama,
       d.tanggal::date AS tanggal_aktif
FROM (
    SELECT pengguna_id, min(waktu) AS mulai, max(waktu) AS selesai
    FROM aktivitas
    GROUP BY pengguna_id
) rentang
INNER JOIN pengguna p ON p.id = rentang.pengguna_id
CROSS JOIN LATERAL generate_series(
    rentang.mulai::date,
    rentang.selesai::date,
    INTERVAL '1 day'
) AS d(tanggal)
ORDER BY p.id, d.tanggal;

-- (2) VALUES di dalam LATERAL: tiap event 'daftar' dikembangkan menjadi dua
-- baris pengingat yang waktunya DIHITUNG dari kolom baris luar (a.waktu).
SELECT a.id AS event_id,
       p.nama,
       a.waktu AS waktu_daftar,
       r.waktu_kirim,
       r.label
FROM aktivitas a
INNER JOIN pengguna p ON p.id = a.pengguna_id
CROSS JOIN LATERAL (VALUES
    (a.waktu + INTERVAL '1 day', 'pengingat H+1'),
    (a.waktu + INTERVAL '3 day', 'pengingat H+3')
) AS r(waktu_kirim, label)
WHERE a.jenis = 'daftar'
ORDER BY a.id, r.waktu_kirim;

Output yang Dihasilkan:

text 
     nama     | tanggal_aktif 
--------------+---------------
 Budi Santoso | 2026-01-04
 Budi Santoso | 2026-01-05
 Budi Santoso | 2026-01-06
 Budi Santoso | 2026-01-07
 Budi Santoso | 2026-01-08
 Siti Rahayu  | 2026-01-04
 Siti Rahayu  | 2026-01-05
 Siti Rahayu  | 2026-01-06
 Siti Rahayu  | 2026-01-07
 Siti Rahayu  | 2026-01-08
 Siti Rahayu  | 2026-01-09
 Siti Rahayu  | 2026-01-10
 Siti Rahayu  | 2026-01-11
 Siti Rahayu  | 2026-01-12
 Dewi Lestari | 2026-01-05
 Dewi Lestari | 2026-01-06
 Dewi Lestari | 2026-01-07
(17 rows)

 event_id |     nama     |    waktu_daftar     |     waktu_kirim     |     label     
----------+--------------+---------------------+---------------------+---------------
      101 | Budi Santoso | 2026-01-04 08:00:00 | 2026-01-05 08:00:00 | pengingat H+1
      101 | Budi Santoso | 2026-01-04 08:00:00 | 2026-01-07 08:00:00 | pengingat H+3
      105 | Siti Rahayu  | 2026-01-04 08:30:00 | 2026-01-05 08:30:00 | pengingat H+1
      105 | Siti Rahayu  | 2026-01-04 08:30:00 | 2026-01-07 08:30:00 | pengingat H+3
      109 | Dewi Lestari | 2026-01-05 20:00:00 | 2026-01-06 20:00:00 | pengingat H+1
      109 | Dewi Lestari | 2026-01-05 20:00:00 | 2026-01-08 20:00:00 | pengingat H+3
(6 rows)

Perhatikan hasilnya: 17 baris tanggal aktif dari hanya 3 pengguna beraktivitas, dan 6 baris pengingat dari 3 event daftar. generate_series dan VALUES di klausa FROM tidak butuh kata kunci LATERAL eksplisit — set-returning function di FROM selalu boleh membaca baris yang mendahuluinya; kata kunci hanya wajib untuk subquery biasa. CROSS JOIN LATERAL dipilih ketika setiap baris luar dijamin menghasilkan minimal satu baris (rentang selalu valid, VALUES selalu dua baris); bila subquery bisa kosong, LEFT JOIN LATERAL ... ON true menjaga baris luar tetap hidup.

Correlated Subquery vs LATERAL #

Correlated subquery di daftar SELECT tampak mengisi peran yang sama. Bedanya fundamental: scalar subquery harus mengembalikan tepat satu baris satu kolom — ia adalah slot nilai, bukan sumber baris. Query berikut membandingkan ketiganya untuk pertanyaan yang sama:

sql lateral-vs-subquery.sql 
-- Correlated subquery vs LATERAL: dua alat yang mirip, kapasitasnya berbeda.
-- Pertanyaan sama: aktivitas TERAKHIR tiap pengguna.

-- (1) Correlated subquery: hanya boleh mengembalikan SATU kolom (scalar).
-- Butuh kolom kedua (jenis, poin)? Logika query ditulis ulang dua kali.
SELECT p.nama,
       (SELECT max(a.waktu)
        FROM aktivitas a
        WHERE a.pengguna_id = p.id) AS waktu_terakhir,
       (SELECT a.jenis
        FROM aktivitas a
        WHERE a.pengguna_id = p.id
        ORDER BY a.waktu DESC
        LIMIT 1) AS jenis_terakhir
FROM pengguna p
ORDER BY p.id;

-- (2) LATERAL mengembalikan baris penuh: satu definisi, semua kolom sekaligus.
SELECT p.nama,
       terakhir.waktu,
       terakhir.jenis,
       terakhir.poin
FROM pengguna p
LEFT JOIN LATERAL (
    SELECT a.waktu, a.jenis, a.poin
    FROM aktivitas a
    WHERE a.pengguna_id = p.id
    ORDER BY a.waktu DESC
    LIMIT 1
) terakhir ON true
ORDER BY p.id;

-- (3) Melebihi satu baris: LATERAL tetap sah -- tiga aktivitas terakhir
-- tiap pengguna, masih satu subquery yang sama dengan LIMIT berubah.
SELECT p.nama,
       tiga.waktu,
       tiga.jenis,
       tiga.poin
FROM pengguna p
LEFT JOIN LATERAL (
    SELECT a.waktu, a.jenis, a.poin
    FROM aktivitas a
    WHERE a.pengguna_id = p.id
    ORDER BY a.waktu DESC
    LIMIT 3
) tiga ON true
ORDER BY p.id, tiga.waktu DESC;

Output yang Dihasilkan:

text 
     nama     |   waktu_terakhir    | jenis_terakhir 
--------------+---------------------+----------------
 Budi Santoso | 2026-01-08 10:00:00 | login
 Siti Rahayu  | 2026-01-12 11:00:00 | beli
 Andi Wijaya  |                     | 
 Dewi Lestari | 2026-01-07 21:00:00 | beli
(4 rows)

     nama     |        waktu        | jenis | poin 
--------------+---------------------+-------+------
 Budi Santoso | 2026-01-08 10:00:00 | login |   10
 Siti Rahayu  | 2026-01-12 11:00:00 | beli  |  700
 Andi Wijaya  |                     |       |     
 Dewi Lestari | 2026-01-07 21:00:00 | beli  |  200
(4 rows)

     nama     |        waktu        | jenis  | poin 
--------------+---------------------+--------+------
 Budi Santoso | 2026-01-08 10:00:00 | login  |   10
 Budi Santoso | 2026-01-05 09:30:00 | beli   |  500
 Budi Santoso | 2026-01-05 08:00:00 | login  |   10
 Siti Rahayu  | 2026-01-12 11:00:00 | beli   |  700
 Siti Rahayu  | 2026-01-10 14:00:00 | login  |   25
 Siti Rahayu  | 2026-01-06 09:00:00 | beli   |  300
 Andi Wijaya  |                     |        |     
 Dewi Lestari | 2026-01-07 21:00:00 | beli   |  200
 Dewi Lestari | 2026-01-06 20:00:00 | login  |   12
 Dewi Lestari | 2026-01-05 20:00:00 | daftar |   50
(10 rows)

Query (1) menghasilkan dua kolom dari dua subquery terpisah — dan keduanya menjalankan pencarian event terakhir secara berbeda (max(waktu) vs ORDER BY ... LIMIT 1) persis karena scalar subquery tidak bisa mengirim dua kolom sekaligus. Query (2) mengambil ketiga kolom dari satu subquery; query (3) cukup mengubah LIMIT 1 menjadi LIMIT 3 untuk melompat dari satu baris ke tiga baris per pengguna — sesuatu yang tidak punya padanan di scalar subquery. Batas keras scalar subquery bahkan bukan sekadar keterbatasan desain; melanggarnya langsung ditolak:

sql subquery-skalar-error.sql 
-- Jebakan scalar subquery: subquery berkorelasi yang mengembalikan lebih dari
-- satu baris ditolak keras. Budi punya 4 aktivitas; tanpa agregat/LIMIT,
-- subquery mengirim 4 nilai ke satu slot nilai tunggal.
SELECT p.nama,
       (SELECT a.waktu
        FROM aktivitas a
        WHERE a.pengguna_id = p.id
        ORDER BY a.waktu) AS waktu_pertama
FROM pengguna p
ORDER BY p.id;

Output yang Dihasilkan:

text 
psql:subquery-skalar-error.sql:10: ERROR:  more than one row returned by a subquery used as an expression

Rangkumannya dalam satu tabel:

AspekCorrelated scalar subqueryJOIN LATERAL
Jumlah baris hasilwajib tepat satu0, 1, atau N per baris luar
Jumlah kolomsatubebas
Dipakai didaftar SELECT, WHEREklausa FROM
Logika berulangtiap kolom = subquery barusatu definisi untuk semua kolom
Join dengan tabel laintidak bisabisa (INNER/LEFT/CROSS)

Ledakan Array dengan Posisi: unnest dan WITH ORDINALITY #

Tabel pengguna menyimpan minat sebagai array. unnest membuka array menjadi baris, tetapi urutan elemen hilang; WITH ORDINALITY (hanya sah bersama fungsi di FROM) mengembalikan posisi asli tiap elemen sebagai kolom tambahan:

sql unnest-posisi.sql 
-- LATERAL + unnest ... WITH ORDINALITY: ledakan array dengan posisi aslinya.
-- unnest saja membuang informasi urutan; WITH ORDINALITY mengembalikannya
-- sebagai kolom kedua (posisi mulai dari 1).
SELECT p.nama,
       m.posisi,
       m.minat
FROM pengguna p
CROSS JOIN LATERAL unnest(p.minat) WITH ORDINALITY AS m(minat, posisi)
ORDER BY p.id, m.posisi;

-- Posisi membuka filter yang tidak bisa dijawakan unnest polos:
-- "minat utama" = elemen pertama tiap array pengguna.
SELECT p.nama,
       m.minat AS minat_utama
FROM pengguna p
CROSS JOIN LATERAL unnest(p.minat) WITH ORDINALITY AS m(minat, posisi)
WHERE m.posisi = 1
ORDER BY p.id;

Output yang Dihasilkan:

text 
     nama     | posisi |  minat   
--------------+--------+----------
 Budi Santoso |      1 | kopi
 Budi Santoso |      2 | sql
 Siti Rahayu  |      1 | sql
 Siti Rahayu  |      2 | bi
 Siti Rahayu  |      3 | kopi
 Andi Wijaya  |      1 | jaringan
 Dewi Lestari |      1 | bi
 Dewi Lestari |      2 | cloud
(8 rows)

     nama     | minat_utama 
--------------+-------------
 Budi Santoso | kopi
 Siti Rahayu  | sql
 Andi Wijaya  | jaringan
 Dewi Lestari | bi
(4 rows)

Query pertama memperlihatkan delapan baris minat dengan posisi 1–3; array Siti {sql, bi, kopi} terbuka dengan urutannya terjaga. Query kedua memanfaatkan posisi itu sebagai filter: minat utama tiap pengguna adalah elemen posisi 1. Subscript p.minat[1] memang bisa menjawab kasus khusus “elemen pertama”, tetapi WITH ORDINALITY memberi posisi untuk setiap elemen — filter posisi <= 2 untuk dua minat utama, atau posisi > 1 untuk minat pelengkap, semuanya tanpa menulis subscript per kasus.

Rantai LATERAL: Pipeline Transformasi per Baris #

Pola terakhir menyusun beberapa LATERAL berturut-turut, masing-masing membaca hasil LATERAL sebelumnya — persis pipeline fungsi kecil yang saling menyambung. Tiga langkah menghasilkan tiga kolom analitik per pengguna: event pertama, kepadatan event di hari pertama, dan event terakhir beserta rentang harinya:

sql rantai-lateral.sql 
-- Rantai LATERAL: pipeline transformasi bertahap, tiap langkah bernama dan
-- hanya membaca langkah sebelumnya -- sama seperti fungsi kecil berurutan.
-- L1 pertama: event pertama tiap pengguna.
-- L2 di_hari_pertama: berapa event terjadi di hari yang sama dengan L1.
-- L3 terakhir: event terakhir tiap pengguna.
SELECT p.nama,
       pertama.waktu AS event_pertama,
       di_hari_pertama.jumlah AS event_hari_pertama,
       terakhir.waktu AS event_terakhir,
       (terakhir.waktu::date - pertama.waktu::date) AS rentang_hari
FROM pengguna p
LEFT JOIN LATERAL (
    SELECT a.waktu
    FROM aktivitas a
    WHERE a.pengguna_id = p.id
    ORDER BY a.waktu
    LIMIT 1
) pertama ON true
LEFT JOIN LATERAL (
    SELECT count(*) AS jumlah
    FROM aktivitas a
    WHERE a.pengguna_id = p.id
      AND a.waktu::date = pertama.waktu::date
) di_hari_pertama ON true
LEFT JOIN LATERAL (
    SELECT a.waktu
    FROM aktivitas a
    WHERE a.pengguna_id = p.id
    ORDER BY a.waktu DESC
    LIMIT 1
) terakhir ON true
ORDER BY p.id;

Output yang Dihasilkan:

text 
     nama     |    event_pertama    | event_hari_pertama |   event_terakhir    | rentang_hari 
--------------+---------------------+--------------------+---------------------+--------------
 Budi Santoso | 2026-01-04 08:00:00 |                  1 | 2026-01-08 10:00:00 |            4
 Siti Rahayu  | 2026-01-04 08:30:00 |                  1 | 2026-01-12 11:00:00 |            8
 Andi Wijaya  |                     |                  0 |                     |             
 Dewi Lestari | 2026-01-05 20:00:00 |                  1 | 2026-01-07 21:00:00 |            2
(4 rows)

Alur pembacaannya kiri ke kanan: pertama menemukan event pertama (Budi: 4 Januari), di_hari_pertama menghitung event yang jatuh pada tanggal hasil pertama (Budi: 1 event di 4 Januari), dan terakhir menutup dengan event terakhir (Budi: 8 Januari) sehingga rentang_hari tinggal mengurangkan keduanya (4 hari). Setiap LEFT JOIN ... ON true menjaga pengguna tanpa aktivitas — Andi tampil dengan kolom kosong dan jumlah 0 karena agregat count(*) selalu mengembalikan satu baris.

LATERAL kuat karena menggandeng logika per baris ke dalam satu query — tetapi ada pekerjaan analitik yang justru menuntut pandangan lintas-baris: nilai baris sebelumnya, akumulasi berjalan, dan peringkat di dalam partisi. Wilayah itu milik window function, dan chapter berikutnya membawa window function itu sendiri ke pola yang lebih dalam: jendela bertingkat, ROWS vs RANGE dengan EXCLUDE, hingga window function di dalam UPDATE.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

agregat-per-baris.sql

Unduh berkas
sql 
-- LATERAL dengan agregat per baris: total poin 3 aktivitas TERAKHIR tiap pengguna.
-- Subquery di dalam LATERAL melihat p.id dari baris luar; LIMIT 3 berlaku
-- per pengguna -- sesuatu yang mustahil bagi subquery FROM biasa.
SELECT p.nama,
       tiga.jumlah_event,
       tiga.total_poin
FROM pengguna p
LEFT JOIN LATERAL (
    SELECT count(*) AS jumlah_event,
           coalesce(sum(poin), 0) AS total_poin
    FROM (
        SELECT a.poin
        FROM aktivitas a
        WHERE a.pengguna_id = p.id
        ORDER BY a.waktu DESC
        LIMIT 3
    ) tiga_terakhir
) tiga ON true
ORDER BY p.id;

buat-tabel.sql

Unduh berkas
sql 
-- Tabel latihan chapter Lateral Join: pengguna dan log aktivitasnya.
-- Andi Wijaya sengaja tanpa aktivitas apa pun (uji LEFT JOIN LATERAL).
CREATE TABLE pengguna (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    minat text[] NOT NULL DEFAULT '{}'
);

CREATE TABLE aktivitas (
    id           integer PRIMARY KEY,
    pengguna_id  integer NOT NULL REFERENCES pengguna (id),
    waktu        timestamp NOT NULL,
    jenis        text NOT NULL,
    poin         integer NOT NULL
);

INSERT INTO pengguna (id, nama, minat) VALUES
    (1, 'Budi Santoso', ARRAY['kopi', 'sql']),
    (2, 'Siti Rahayu',  ARRAY['sql', 'bi', 'kopi']),
    (3, 'Andi Wijaya',  ARRAY['jaringan']),
    (4, 'Dewi Lestari', ARRAY['bi', 'cloud']);

INSERT INTO aktivitas (id, pengguna_id, waktu, jenis, poin) VALUES
    (101, 1, TIMESTAMP '2026-01-04 08:00:00', 'daftar',   50),
    (102, 1, TIMESTAMP '2026-01-05 08:00:00', 'login',    10),
    (103, 1, TIMESTAMP '2026-01-05 09:30:00', 'beli',    500),
    (104, 1, TIMESTAMP '2026-01-08 10:00:00', 'login',    10),
    (105, 2, TIMESTAMP '2026-01-04 08:30:00', 'daftar',   50),
    (106, 2, TIMESTAMP '2026-01-06 09:00:00', 'beli',    300),
    (107, 2, TIMESTAMP '2026-01-10 14:00:00', 'login',    25),
    (108, 2, TIMESTAMP '2026-01-12 11:00:00', 'beli',    700),
    (109, 4, TIMESTAMP '2026-01-05 20:00:00', 'daftar',   50),
    (110, 4, TIMESTAMP '2026-01-06 20:00:00', 'login',    12),
    (111, 4, TIMESTAMP '2026-01-07 21:00:00', 'beli',    200);

SELECT id, nama, minat FROM pengguna ORDER BY id;

SELECT id, pengguna_id, waktu, jenis, poin
FROM aktivitas
ORDER BY id;

lateral-vs-subquery.sql

Unduh berkas
sql 
-- Correlated subquery vs LATERAL: dua alat yang mirip, kapasitasnya berbeda.
-- Pertanyaan sama: aktivitas TERAKHIR tiap pengguna.

-- (1) Correlated subquery: hanya boleh mengembalikan SATU kolom (scalar).
-- Butuh kolom kedua (jenis, poin)? Logika query ditulis ulang dua kali.
SELECT p.nama,
       (SELECT max(a.waktu)
        FROM aktivitas a
        WHERE a.pengguna_id = p.id) AS waktu_terakhir,
       (SELECT a.jenis
        FROM aktivitas a
        WHERE a.pengguna_id = p.id
        ORDER BY a.waktu DESC
        LIMIT 1) AS jenis_terakhir
FROM pengguna p
ORDER BY p.id;

-- (2) LATERAL mengembalikan baris penuh: satu definisi, semua kolom sekaligus.
SELECT p.nama,
       terakhir.waktu,
       terakhir.jenis,
       terakhir.poin
FROM pengguna p
LEFT JOIN LATERAL (
    SELECT a.waktu, a.jenis, a.poin
    FROM aktivitas a
    WHERE a.pengguna_id = p.id
    ORDER BY a.waktu DESC
    LIMIT 1
) terakhir ON true
ORDER BY p.id;

-- (3) Melebihi satu baris: LATERAL tetap sah -- tiga aktivitas terakhir
-- tiap pengguna, masih satu subquery yang sama dengan LIMIT berubah.
SELECT p.nama,
       tiga.waktu,
       tiga.jenis,
       tiga.poin
FROM pengguna p
LEFT JOIN LATERAL (
    SELECT a.waktu, a.jenis, a.poin
    FROM aktivitas a
    WHERE a.pengguna_id = p.id
    ORDER BY a.waktu DESC
    LIMIT 3
) tiga ON true
ORDER BY p.id, tiga.waktu DESC;

loop-implisit.sql

Unduh berkas
sql 
-- LATERAL sebagai loop implisit: satu definisi dievaluasi sekali per baris luar.

-- (1) generate_series dengan BATAS PER BARIS: deret tanggal dari event pertama
-- sampai event terakhir tiap pengguna. Panjang deret berbeda-beda per pengguna.
SELECT p.nama,
       d.tanggal::date AS tanggal_aktif
FROM (
    SELECT pengguna_id, min(waktu) AS mulai, max(waktu) AS selesai
    FROM aktivitas
    GROUP BY pengguna_id
) rentang
INNER JOIN pengguna p ON p.id = rentang.pengguna_id
CROSS JOIN LATERAL generate_series(
    rentang.mulai::date,
    rentang.selesai::date,
    INTERVAL '1 day'
) AS d(tanggal)
ORDER BY p.id, d.tanggal;

-- (2) VALUES di dalam LATERAL: tiap event 'daftar' dikembangkan menjadi dua
-- baris pengingat yang waktunya DIHITUNG dari kolom baris luar (a.waktu).
SELECT a.id AS event_id,
       p.nama,
       a.waktu AS waktu_daftar,
       r.waktu_kirim,
       r.label
FROM aktivitas a
INNER JOIN pengguna p ON p.id = a.pengguna_id
CROSS JOIN LATERAL (VALUES
    (a.waktu + INTERVAL '1 day', 'pengingat H+1'),
    (a.waktu + INTERVAL '3 day', 'pengingat H+3')
) AS r(waktu_kirim, label)
WHERE a.jenis = 'daftar'
ORDER BY a.id, r.waktu_kirim;

rantai-lateral.sql

Unduh berkas
sql 
-- Rantai LATERAL: pipeline transformasi bertahap, tiap langkah bernama dan
-- hanya membaca langkah sebelumnya -- sama seperti fungsi kecil berurutan.
-- L1 pertama: event pertama tiap pengguna.
-- L2 di_hari_pertama: berapa event terjadi di hari yang sama dengan L1.
-- L3 terakhir: event terakhir tiap pengguna.
SELECT p.nama,
       pertama.waktu AS event_pertama,
       di_hari_pertama.jumlah AS event_hari_pertama,
       terakhir.waktu AS event_terakhir,
       (terakhir.waktu::date - pertama.waktu::date) AS rentang_hari
FROM pengguna p
LEFT JOIN LATERAL (
    SELECT a.waktu
    FROM aktivitas a
    WHERE a.pengguna_id = p.id
    ORDER BY a.waktu
    LIMIT 1
) pertama ON true
LEFT JOIN LATERAL (
    SELECT count(*) AS jumlah
    FROM aktivitas a
    WHERE a.pengguna_id = p.id
      AND a.waktu::date = pertama.waktu::date
) di_hari_pertama ON true
LEFT JOIN LATERAL (
    SELECT a.waktu
    FROM aktivitas a
    WHERE a.pengguna_id = p.id
    ORDER BY a.waktu DESC
    LIMIT 1
) terakhir ON true
ORDER BY p.id;

subquery-skalar-error.sql

Unduh berkas
sql 
-- Jebakan scalar subquery: subquery berkorelasi yang mengembalikan lebih dari
-- satu baris ditolak keras. Budi punya 4 aktivitas; tanpa agregat/LIMIT,
-- subquery mengirim 4 nilai ke satu slot nilai tunggal.
SELECT p.nama,
       (SELECT a.waktu
        FROM aktivitas a
        WHERE a.pengguna_id = p.id
        ORDER BY a.waktu) AS waktu_pertama
FROM pengguna p
ORDER BY p.id;

unnest-posisi.sql

Unduh berkas
sql 
-- LATERAL + unnest ... WITH ORDINALITY: ledakan array dengan posisi aslinya.
-- unnest saja membuang informasi urutan; WITH ORDINALITY mengembalikannya
-- sebagai kolom kedua (posisi mulai dari 1).
SELECT p.nama,
       m.posisi,
       m.minat
FROM pengguna p
CROSS JOIN LATERAL unnest(p.minat) WITH ORDINALITY AS m(minat, posisi)
ORDER BY p.id, m.posisi;

-- Posisi membuka filter yang tidak bisa dijawakan unnest polos:
-- "minat utama" = elemen pertama tiap array pengguna.
SELECT p.nama,
       m.minat AS minat_utama
FROM pengguna p
CROSS JOIN LATERAL unnest(p.minat) WITH ORDINALITY AS m(minat, posisi)
WHERE m.posisi = 1
ORDER BY p.id;