Subquery Berkorelasi

Subquery yang merujuk baris luar beserta biayanya dan batas kapan dipakai.

Daftar isi

Subquery berkorelasi (correlated subquery) adalah subquery yang merujuk kolom dari query luarnya, sehingga secara semantik dinilai ulang untuk setiap baris luar. Topik ini mendefinisikan mekanismenya, memperagakan contoh yang memang membutuhkan korelasi, membahas biaya pada tabel besar, lalu membandingkan penulisan ulang yang sama sebagai JOIN beserta perbedaan rencana querynya. Dataset tabel buku dan kategori yang sama dengan topik lain di modul ini dipakai sepanjang contoh, sehingga hasil setiap query dapat dibandingkan secara langsung.

Definisi dan cara kerja#

Subquery tanpa korelasi menghasilkan satu nilai yang sama untuk seluruh baris luar karena tidak merujuk kolom apa pun dari luar. Begitu subquery memuat referensi ke kolom baris luar, misalnya melalui alias tabel, hubungannya berubah. Secara semantik, subquery dibayangkan dijalankan ulang untuk setiap baris luar. Nilai kolom rujukan diambil dari baris yang sedang dinilai, dan baris luar lolos atau gagal bergantung pada hasil tersebut.

Perencana query SQLite berhak mengoptimalkan bentuk ini, tetapi hasil akhirnya wajib setara dengan evaluasi ulang baris demi baris tersebut. Ketergantungan semacam ini menjadi penting karena pertanyaan yang sama kerap dapat ditulis dengan cara yang biayanya berbeda jauh. Bagian berikut menunjukkan pertanyaan yang secara alami menuntut korelasi, yaitu membandingkan setiap baris dengan statistik kelompoknya sendiri.

Contoh: buku di atas rata-rata kategorinya#

Pertanyaannya adalah memilih buku yang tahun terbitnya berada di atas rata-rata tahun terbit kategorinya masing-masing. Rumusan ini tidak dapat ditulis dengan GROUP BY biasa, karena GROUP BY mengerucutkan setiap kategori menjadi satu baris agregat dan kehilangan baris bukunya. Nilai pembanding harus dihitung per kategori, sedangkan baris yang dinilai tetap berupa buku. Lihat dahulu rata-rata per kategori agar angkanya jelas.

sql Rata-rata tahun terbit per kategori

CREATE TABLE kategori (
  id       INTEGER PRIMARY KEY,
  nama     TEXT NOT NULL,
  id_induk INTEGER REFERENCES kategori(id)
);
CREATE TABLE buku (
  id          INTEGER PRIMARY KEY,
  judul       TEXT NOT NULL,
  tahun       INTEGER NOT NULL,
  id_kategori INTEGER REFERENCES kategori(id)
);
INSERT INTO kategori (id, nama, id_induk) VALUES
  (1, 'Ilmu Komputer', NULL),
  (2, 'Sastra',        NULL),
  (3, 'Sejarah',       NULL),
  (4, 'Algoritma',     1),
  (5, 'Jaringan',      1),
  (6, 'Puisi',         2),
  (7, 'Prosa',         2);
INSERT INTO buku (id, judul, tahun, id_kategori) VALUES
  (1, 'Dasar Algoritma',        2018, 4),
  (2, 'Struktur Data Terapan',  2021, 4),
  (3, 'Analisis Kompleksitas',  2019, 4),
  (4, 'Jaringan Komputer',      2020, 5),
  (5, 'Protokol Internet',      2023, 5),
  (6, 'Kumpulan Puisi Lama',    2012, 6),
  (7, 'Antologi Puisi Baru',    2022, 6),
  (8, 'Kamus Istilah Komputer', 2024, 1),
  (9, 'Ensiklopedia Sastra',    2009, NULL);

SELECT k.nama,
       COUNT(*)     AS jumlah_buku,
       AVG(b.tahun) AS rata_tahun
FROM kategori AS k
JOIN buku AS b ON b.id_kategori = k.id
GROUP BY k.id;

text Keluaran

Ilmu Komputer|1|2024.0
Algoritma|3|2019.3333333333333
Jaringan|2|2021.5
Puisi|2|2017.0

Rata-rata Algoritma sekitar 2019,33, Jaringan 2021,5, dan Puisi 2017, sedangkan Ilmu Komputer tepat 2024 karena bukunya hanya satu. Subquery berkorelasi berikut menghitung rata-rata yang sama untuk kategori buku yang sedang dinilai, lalu membandingkan tahun terbitnya.

sql Buku dengan tahun di atas rata-rata kategorinya

CREATE TABLE kategori (
  id       INTEGER PRIMARY KEY,
  nama     TEXT NOT NULL,
  id_induk INTEGER REFERENCES kategori(id)
);
CREATE TABLE buku (
  id          INTEGER PRIMARY KEY,
  judul       TEXT NOT NULL,
  tahun       INTEGER NOT NULL,
  id_kategori INTEGER REFERENCES kategori(id)
);
INSERT INTO kategori (id, nama, id_induk) VALUES
  (1, 'Ilmu Komputer', NULL),
  (2, 'Sastra',        NULL),
  (3, 'Sejarah',       NULL),
  (4, 'Algoritma',     1),
  (5, 'Jaringan',      1),
  (6, 'Puisi',         2),
  (7, 'Prosa',         2);
INSERT INTO buku (id, judul, tahun, id_kategori) VALUES
  (1, 'Dasar Algoritma',        2018, 4),
  (2, 'Struktur Data Terapan',  2021, 4),
  (3, 'Analisis Kompleksitas',  2019, 4),
  (4, 'Jaringan Komputer',      2020, 5),
  (5, 'Protokol Internet',      2023, 5),
  (6, 'Kumpulan Puisi Lama',    2012, 6),
  (7, 'Antologi Puisi Baru',    2022, 6),
  (8, 'Kamus Istilah Komputer', 2024, 1),
  (9, 'Ensiklopedia Sastra',    2009, NULL);
SELECT b.judul, b.tahun
FROM buku AS b
WHERE b.tahun > (
  SELECT AVG(b2.tahun)
  FROM buku AS b2
  WHERE b2.id_kategori = b.id_kategori
)
ORDER BY b.tahun;

text Keluaran

Struktur Data Terapan|2021
Antologi Puisi Baru|2022
Protokol Internet|2023

Hasilnya tiga buku, dan setiap buku lolos terhadap pembandingnya sendiri, bukan terhadap rata-rata seluruh tabel. Buku Kamus Istilah Komputer tidak lolos karena tahun 2024 sama dengan rata-rata kategorinya, sedangkan Ensiklopedia Sastra ikut tersaring karena subquery untuk baris tanpa kategori menghasilkan NULL. Perhatikan bagaimana alias b dan b2 memisahkan baris luar dari baris dalam, yang merupakan syarat penulisan bentuk ini.

Kewaspadaan biaya#

Bentuk korelasi membayar keterbacaan semantiknya dengan kerja yang berlipat. Apabila baris luar berjumlah N dan penilaian di dalam memerlukan pemindaian M baris, total pekerjaan secara konseptual berada pada orde N dikali M, karena setiap baris luar memicu evaluasi subquerynya sendiri. Pada dataset kecil seperti contoh ini selisihnya tidak terasa, tetapi pola yang sama pada tabel berukuran produksi dengan ratusan ribu baris dapat terasa lambat.

Indeks pada kolom pencarian di dalam subquery mengubah pemindaian penuh menjadi pencarian langsung, sehingga biaya per baris luar turun banyak. Cara memeriksanya adalah membaca rencana query dengan EXPLAIN QUERY PLAN, yang menampilkan struktur pekerjaan tanpa mengukur waktu, sehingga keluarannya stabil dan layak dibandingkan antarversi query. Pengukuran durasi memang berguna, tetapi rencana query memberi gambaran penyebab, bukan sekadar gejala.

Penulisan ulang sebagai JOIN dan perbandingan rencana#

Hasil yang sama dapat ditulis tanpa korelasi dengan menghitung rata-rata per kategori lebih dahulu, lalu menggabungkannya kembali ke tabel buku. Bentuk subquery di dalam FROM berikut disebut tabel turunan (derived table) karena hasilnya diperlakukan sebagai tabel sementara.

sql Hasil yang sama dengan tabel turunan

CREATE TABLE kategori (
  id       INTEGER PRIMARY KEY,
  nama     TEXT NOT NULL,
  id_induk INTEGER REFERENCES kategori(id)
);
CREATE TABLE buku (
  id          INTEGER PRIMARY KEY,
  judul       TEXT NOT NULL,
  tahun       INTEGER NOT NULL,
  id_kategori INTEGER REFERENCES kategori(id)
);
INSERT INTO kategori (id, nama, id_induk) VALUES
  (1, 'Ilmu Komputer', NULL),
  (2, 'Sastra',        NULL),
  (3, 'Sejarah',       NULL),
  (4, 'Algoritma',     1),
  (5, 'Jaringan',      1),
  (6, 'Puisi',         2),
  (7, 'Prosa',         2);
INSERT INTO buku (id, judul, tahun, id_kategori) VALUES
  (1, 'Dasar Algoritma',        2018, 4),
  (2, 'Struktur Data Terapan',  2021, 4),
  (3, 'Analisis Kompleksitas',  2019, 4),
  (4, 'Jaringan Komputer',      2020, 5),
  (5, 'Protokol Internet',      2023, 5),
  (6, 'Kumpulan Puisi Lama',    2012, 6),
  (7, 'Antologi Puisi Baru',    2022, 6),
  (8, 'Kamus Istilah Komputer', 2024, 1),
  (9, 'Ensiklopedia Sastra',    2009, NULL);
SELECT b.judul, b.tahun
FROM buku AS b
JOIN (
  SELECT id_kategori, AVG(tahun) AS rata_tahun
  FROM buku
  GROUP BY id_kategori
) AS rata ON rata.id_kategori = b.id_kategori
WHERE b.tahun > rata.rata_tahun
ORDER BY b.tahun;

text Keluaran

Struktur Data Terapan|2021
Antologi Puisi Baru|2022
Protokol Internet|2023

Rencana kedua bentuk itu pada tabel yang sama dapat dilihat dengan EXPLAIN QUERY PLAN. Versi berkorelasi memperlihatkan subquery yang dilabeli CORRELATED dengan pemindaian b2 di dalamnya, sedangkan versi tabel turunan mengevaluasi agregatnya sekali sebagai CO-ROUTINE, kemudian mencocokkan setiap baris buku ke hasil itu melalui indeks otomatis.

sql Rencana query subquery berkorelasi

CREATE TABLE kategori (
  id       INTEGER PRIMARY KEY,
  nama     TEXT NOT NULL,
  id_induk INTEGER REFERENCES kategori(id)
);
CREATE TABLE buku (
  id          INTEGER PRIMARY KEY,
  judul       TEXT NOT NULL,
  tahun       INTEGER NOT NULL,
  id_kategori INTEGER REFERENCES kategori(id)
);
INSERT INTO kategori (id, nama, id_induk) VALUES
  (1, 'Ilmu Komputer', NULL),
  (2, 'Sastra',        NULL),
  (3, 'Sejarah',       NULL),
  (4, 'Algoritma',     1),
  (5, 'Jaringan',      1),
  (6, 'Puisi',         2),
  (7, 'Prosa',         2);
INSERT INTO buku (id, judul, tahun, id_kategori) VALUES
  (1, 'Dasar Algoritma',        2018, 4),
  (2, 'Struktur Data Terapan',  2021, 4),
  (3, 'Analisis Kompleksitas',  2019, 4),
  (4, 'Jaringan Komputer',      2020, 5),
  (5, 'Protokol Internet',      2023, 5),
  (6, 'Kumpulan Puisi Lama',    2012, 6),
  (7, 'Antologi Puisi Baru',    2022, 6),
  (8, 'Kamus Istilah Komputer', 2024, 1),
  (9, 'Ensiklopedia Sastra',    2009, NULL);
EXPLAIN QUERY PLAN
SELECT b.judul, b.tahun
FROM buku AS b
WHERE b.tahun > (
  SELECT AVG(b2.tahun)
  FROM buku AS b2
  WHERE b2.id_kategori = b.id_kategori
);

text Keluaran

QUERY PLAN
|--SCAN b
`--CORRELATED SCALAR SUBQUERY 1
   `--SCAN b2

sql Rencana query versi tabel turunan

CREATE TABLE kategori (
  id       INTEGER PRIMARY KEY,
  nama     TEXT NOT NULL,
  id_induk INTEGER REFERENCES kategori(id)
);
CREATE TABLE buku (
  id          INTEGER PRIMARY KEY,
  judul       TEXT NOT NULL,
  tahun       INTEGER NOT NULL,
  id_kategori INTEGER REFERENCES kategori(id)
);
INSERT INTO kategori (id, nama, id_induk) VALUES
  (1, 'Ilmu Komputer', NULL),
  (2, 'Sastra',        NULL),
  (3, 'Sejarah',       NULL),
  (4, 'Algoritma',     1),
  (5, 'Jaringan',      1),
  (6, 'Puisi',         2),
  (7, 'Prosa',         2);
INSERT INTO buku (id, judul, tahun, id_kategori) VALUES
  (1, 'Dasar Algoritma',        2018, 4),
  (2, 'Struktur Data Terapan',  2021, 4),
  (3, 'Analisis Kompleksitas',  2019, 4),
  (4, 'Jaringan Komputer',      2020, 5),
  (5, 'Protokol Internet',      2023, 5),
  (6, 'Kumpulan Puisi Lama',    2012, 6),
  (7, 'Antologi Puisi Baru',    2022, 6),
  (8, 'Kamus Istilah Komputer', 2024, 1),
  (9, 'Ensiklopedia Sastra',    2009, NULL);
EXPLAIN QUERY PLAN
SELECT b.judul, b.tahun
FROM buku AS b
JOIN (
  SELECT id_kategori, AVG(tahun) AS rata_tahun
  FROM buku
  GROUP BY id_kategori
) AS rata ON rata.id_kategori = b.id_kategori
WHERE b.tahun > rata.rata_tahun;

text Keluaran

QUERY PLAN
|--CO-ROUTINE rata
|  |--SCAN buku
|  `--USE TEMP B-TREE FOR GROUP BY
|--SCAN b
|--BLOOM FILTER ON rata (id_kategori=?)
`--SEARCH rata USING AUTOMATIC COVERING INDEX (id_kategori=?)

Perbedaannya terbaca langsung, yaitu label CORRELATED SCALAR SUBQUERY dengan pemindaian berulang pada versi pertama, dibandingkan agregat tunggal dan pencarian berindeks pada versi kedua. Buku Ensiklopedia Sastra dengan kategori NULL juga hilang dengan sendirinya pada versi tabel turunan karena tidak cocok dengan syarat JOIN.

Ringkasan#

Subquery berkorelasi merujuk kolom baris luar, sehingga secara semantik dinilai ulang baris demi baris.
Perbandingan setiap baris dengan rata-rata grupnya adalah contoh yang paling alami bagi bentuk ini.
Biayanya tumbuh sebagai kelipatan baris luar dan baris dalam, sehingga pada tabel besar rencana query perlu diperiksa.
Bentuk agregat dengan JOIN menghasilkan keluaran yang sama, lebih mudah ditelaah, dan mengevaluasi agregatnya satu kali.

Referensi#

SELECT — Dokumentasi SQLite — Sintaks SELECT dengan subquery dalam ekspresi, FROM, dan GROUP BY.
Ekspresi — Dokumentasi SQLite — Aturan penilaian subquery dalam ekspresi, termasuk subquery yang merujuk baris luar.