KAL
Menu

Subquery berkorelasi

Menjalankan subquery yang merujuk baris luar beserta pertimbangan biayanya.

Daftar isi

Topik ini membahas subquery berkorelasi, yaitu subquery yang merujuk kolom dari query luarnya, sehingga hasilnya dinilai dalam konteks setiap baris luar. Bentuk ini menyelesaikan pertanyaan yang membandingkan baris dengan kelompoknya sendiri, dan biayanya menjadi bagian dari pembahasan karena sifat itulah yang menentukan kapan bentuk ini dipilih.

Blok penyiapan dataset#

bash 
# siapkan ulang tabel dan data contoh untuk topik ini
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori CASCADE;
CREATE TABLE kategori (id SERIAL PRIMARY KEY, nama TEXT NOT NULL UNIQUE);
CREATE TABLE anggota (id SERIAL PRIMARY KEY, nama TEXT NOT NULL, email TEXT UNIQUE, bergabung DATE NOT NULL, aktif BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE buku (id SERIAL PRIMARY KEY, judul TEXT NOT NULL, penulis TEXT NOT NULL, tahun INTEGER NOT NULL, kategori_id INTEGER REFERENCES kategori(id));
CREATE TABLE peminjaman (id SERIAL PRIMARY KEY, anggota_id INTEGER NOT NULL REFERENCES anggota(id), buku_id INTEGER NOT NULL REFERENCES buku(id), tanggal_pinjam DATE NOT NULL, tanggal_kembali DATE);
INSERT INTO kategori (nama) VALUES ('Pemrograman'), ('Sains'), ('Sejarah'), ('Fiksi'), ('Statistika');
INSERT INTO anggota (nama, email, bergabung, aktif) VALUES ('Ali Rahman', '[email protected]', '2024-01-15', TRUE), ('Budi Santoso', '[email protected]', '2024-03-02', TRUE), ('Citra Kirana', '[email protected]', '2024-05-20', FALSE), ('Dewi Lestari', '[email protected]', '2024-08-11', TRUE), ('Eka Pratama', NULL, '2025-01-30', TRUE);
INSERT INTO buku (judul, penulis, tahun, kategori_id) VALUES ('Dasar Basis Data', 'Sari Wulandari', 2021, 1), ('Jaringan Komputer', 'Ahmad Fauzi', 2019, 1), ('Fisika untuk Pemula', 'Rina Marlina', 2020, 2), ('Kimia Organik', 'Rina Marlina', 2022, 2), ('Sejarah Nusantara', 'Bambang Wijaya', 2018, 3), ('Cerita Kota Lama', 'Nur Aisyah', 2023, 4), ('Statistika Terapan', 'Sari Wulandari', 2022, 5), ('Algoritma dan Struktur Data', 'Ahmad Fauzi', 2021, 1);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES (1, 1, '2025-03-01', '2025-03-15'), (1, 7, '2025-03-05', '2025-03-20'), (2, 3, '2025-03-10', NULL), (3, 5, '2025-02-20', '2025-03-01'), (2, 1, '2025-04-02', NULL), (4, 6, '2025-04-07', NULL), (5, 8, '2025-04-09', '2025-04-25'), (4, 2, '2025-05-01', NULL);
SQL

Korelasi per baris#

Subquery berkorelasi menyebut alias dari query luar, sehingga maknanya berganti menurut baris yang sedang dinilai. Query berikut menghitung banyaknya peminjaman setiap anggota tanpa GROUP BY pada query luar, karena agregasi terjadi di dalam subquery untuk setiap anggota.

bash 
# setiap anggota beserta jumlah peminjamannya
psql -U postgres -d perpustakaan -c "SELECT a.nama, (SELECT COUNT(*) FROM peminjaman p WHERE p.anggota_id = a.id) AS pinjaman FROM anggota a ORDER BY a.id;"
text Keluaran 
     nama     | pinjaman
--------------+----------
 Ali Rahman   |        2
 Budi Santoso |        2
 Citra Kirana |        1
 Dewi Lestari |        2
 Eka Pratama  |        1
(5 rows)

Subquery dijalankan untuk setiap baris anggota, dan anggota tanpa peminjaman tetap tampil dengan nilai nol karena COUNT atas himpunan kosong sah. Pola ini setara dengan join dan GROUP BY yang dibahas pada modul agregasi, dan pilihannya dibahas pada bagian biaya.

Membandingkan baris dengan kelompoknya#

Kekuatan korelasi muncul ketika perbandingannya melibatkan ringkasan kelompok yang sama, misalnya buku yang terbit paling baru di penulisnya masing-masing. Perbandingan semacam ini tidak dapat ditulis dengan agregat tunggal atas seluruh tabel.

bash 
# buku terbaru pada penulisnya masing-masing
psql -U postgres -d perpustakaan -c "SELECT penulis, judul, tahun FROM buku b WHERE b.tahun = (SELECT MAX(b2.tahun) FROM buku b2 WHERE b2.penulis = b.penulis) ORDER BY penulis, judul;"
text Keluaran 
    penulis     |            judul            | tahun
----------------+-----------------------------+-------
 Ahmad Fauzi    | Algoritma dan Struktur Data |  2021
 Bambang Wijaya | Sejarah Nusantara           |  2018
 Nur Aisyah     | Cerita Kota Lama            |  2023
 Rina Marlina   | Kimia Organik               |  2022
 Sari Wulandari | Statistika Terapan          |  2022
(5 rows)

Setiap baris buku dibandingkan dengan maksimum tahun terbit penulisnya sendiri, dan penulis dengan dua karya sekaligus menyumbang dua baris bila tahunnya seri. Bentuk serupa melayani pertanyaan di atas rata-rata kelompok, dan window function pada dua topik tersendiri menawarkan cara lain untuk kelas masalah yang sama.

Biaya dan alternatifnya#

Secara konseptual, subquery berkorelasi dijalankan sekali per baris luar, sehingga biayanya tumbuh seiring banyaknya baris yang dinilai. Perencana PostgreSQL modern mampu mengangkat sebagian bentuk menjadi subplan yang efisien, tetapi mengandalkan kemampuan itu tanpa memeriksa rencananya bukan kebiasaan yang sehat, dan penulis query menimbang biayanya secara sadar.

Tiga alternatif menangani kelas masalah yang sama. Join dan GROUP BY mengagregasi sekali lalu menyandingkan hasilnya, window function menghitung ringkasan per kelompok tanpa menciutkan baris, dan CTE memisahkan perhitungan ringkasan menjadi langkah bernama. Untuk EXISTS yang berhenti pada temuan pertama, korelasinya justru hemat karena tidak menghitung apa pun penuh, sehingga pilihan akhir mengikuti bentuk pertanyaannya, bukan kebiasaan.

Kesalahan umum#

Kesalahan pertama adalah korelasi yang tidak disengaja, yaitu subquery yang tanpa sengaja merujuk alias luar, sehingga biayanya berubah diam-diam dan hasilnya berganti makna. Kesalahan kedua adalah korelasi pada tabel yang besar untuk agregat penuh, misalnya COUNT di dalam subquery untuk jutaan baris luar, yang lebih tepat ditulis sebagai join dan GROUP BY.

Kesalahan ketiga adalah membandingkan hasil korelasi dengan NULL secara langsung, karena perbandingannya tidak pernah benar, dan bentuk yang aman memakai IS DISTINCT FROM atau memastikan kolomnya NOT NULL. Kesalahan keempat adalah mengira korelasi selalu lambat, padahal EXISTS berkorelasi yang berhenti dini justru menjadi bentuk yang paling murah untuk pertanyaan keberadaan.

EXISTS berkorelasi dan join dengan DISTINCT sering menjawab pertanyaan keberadaan yang sama, dan pilihannya dibaca dari maknanya, yaitu EXISTS menyatakan kepentingan pada keberadaan saja sedangkan join menyatakan kepentingan pada pasangannya. Perencana menghasilkan rencana yang serupa untuk keduanya pada kasus sederhana, sehingga pilihan yang berpihak pada makna tidak pernah menyesatkan performa. Pembacaan rencananya tetap menjadi langkah penutup, sebagaimana semua keputusan performa pada modul ini.

Ringkasan#

  • Subquery berkorelasi merujuk alias query luar, sehingga nilainya dinilai dalam konteks setiap baris luar.
  • Bentuknya menyelesaikan perbandingan baris terhadap kelompoknya sendiri yang mustahil ditulis dengan agregat tunggal.
  • Biaya konseptualnya sekali per baris luar, dan alternatif join, GROUP BY, atau window function menang pada agregasi penuh.
  • EXISTS berkorelasi tetap hemat karena berhenti pada temuan pertama, sehingga pilihan bentuk mengikuti jenis pertanyaan.

Referensi#