KAL
Menu

Subquery

Subquery skalar, IN, dan EXISTS untuk pertanyaan bertingkat.

Daftar isi

Subquery adalah query SELECT yang ditulis di dalam query lain untuk membantu pertanyaan yang tidak dapat diselesaikan dalam satu tahap. Topik ini membahas subquery skalar pada klausa SELECT dan WHERE, pemfilteran dengan IN dan EXISTS, serta perilaku hasil bila subquery mengembalikan nol baris atau lebih dari satu baris. Seluruh contoh menggunakan dua tabel yang konsisten di seluruh modul ini, yaitu tabel kategori(id, nama, id_induk) dengan hierarki dua tingkat dan tabel buku(id, judul, tahun, id_kategori). Setelah membaca topik ini, query bertingkat sederhana dapat ditulis, dibaca, dan dievaluasi hasilnya dengan benar.

Subquery skalar di SELECT dan WHERE#

Subquery skalar (scalar subquery) adalah subquery yang hasilnya tepat satu nilai, sehingga boleh ditempatkan di posisi mana pun yang menerima satu ekspresi. Bentuk yang paling umum adalah perbandingan di WHERE, misalnya mencari buku dengan tahun terbit paling baru. Nilai tahun terbaru tidak perlu diketahui lebih dahulu karena subquery menghitungnya sendiri pada saat query dijalankan.

sql Buku dengan tahun paling baru 
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 judul, tahun
FROM buku
WHERE tahun = (SELECT MAX(tahun) FROM buku);
text Keluaran 
Kamus Istilah Komputer|2024

Subquery juga boleh berada di daftar SELECT untuk menghitung kolom tambahan. Query berikut menampilkan selisih tahun antara setiap buku dan buku terbaru, dengan subquery yang sama dipakai ulang pada setiap baris keluaran.

sql Selisih tahun tiap buku dari buku terbaru 
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 judul,
       tahun,
       (SELECT MAX(tahun) FROM buku) - tahun AS selisih_tahun
FROM buku
ORDER BY tahun DESC;
text Keluaran 
Kamus Istilah Komputer|2024|0
Protokol Internet|2023|1
Antologi Puisi Baru|2022|2
Struktur Data Terapan|2021|3
Jaringan Komputer|2020|4
Analisis Kompleksitas|2019|5
Dasar Algoritma|2018|6
Kumpulan Puisi Lama|2012|12
Ensiklopedia Sastra|2009|15

Kedua subquery di atas tidak merujuk kolom apa pun dari query luarnya, sehingga SQLite cukup mengevaluasinya satu kali dan menggunakan nilainya untuk seluruh baris. Pola tanpa rujukan ke luar ini disebut subquery yang tidak berkorelasi dan merupakan bentuk termudah untuk ditelaah. Karena setiap blok kode pada modul ini membuat tabelnya sendiri dari awal, pernyataan setup yang sama diulang pada setiap blok agar setiap blok dapat dijalankan sendiri-sendiri.

IN dengan daftar hasil subquery#

Operator IN menguji keanggotaan sebuah nilai dalam daftar, dan daftar itu boleh berasal dari hasil subquery. Bentuknya adalah nilai IN (SELECT ...), yaitu baris luar lolos bila nilainya cocok dengan salah satu baris hasil subquery. Pola ini tepat dipakai ketika kriteria pemfilteran sendiri merupakan hasil pencarian, misalnya memilih semua buku yang tercatat pada kategori anak dari Ilmu Komputer. Nilai id anak tersebut tidak dituliskan secara manual, tetapi dicari terlebih dahulu dari kolom id_induk.

sql Buku pada kategori anak Ilmu Komputer 
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 judul, tahun
FROM buku
WHERE id_kategori IN (SELECT id FROM kategori WHERE id_induk = 1)
ORDER BY tahun;
text Keluaran 
Dasar Algoritma|2018
Analisis Kompleksitas|2019
Jaringan Komputer|2020
Struktur Data Terapan|2021
Protokol Internet|2023

Subquery mengembalikan id 4 dan 5, yaitu Algoritma dan Jaringan, sehingga kelima buku pada kedua kategori tersebut lolos. Satu hal yang perlu dijaga adalah kolom id_kategori pada tabel buku boleh berisi NULL. Nilai NULL tidak pernah cocok dengan anggota daftar mana pun, sehingga buku Ensiklopedia Sastra yang kategorinya tidak diisi tidak muncul pada hasil di atas. Perilaku tersebut bukan kekhususan IN, melainkan akibat umum dari perbandingan terhadap NULL.

EXISTS dengan korelasi ringan#

Operator EXISTS menguji keberadaan baris, bukan isi nilainya. Subquery di dalamnya dijalankan dan, begitu ada satu baris saja yang memenuhi syarat, hasilnya menjadi benar dan pemeriksaan berhenti. Bentuk ini lazim dipadukan dengan korelasi ringan, yaitu subquery yang merujuk satu kolom dari baris luar melalui alias tabel. Contoh berikut memilih kategori yang memiliki setidaknya satu buku.

sql Kategori yang memiliki buku 
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
FROM kategori AS k
WHERE EXISTS (
  SELECT 1
  FROM buku AS b
  WHERE b.id_kategori = k.id
)
ORDER BY k.nama;
text Keluaran 
Algoritma
Ilmu Komputer
Jaringan
Puisi

Dari tujuh kategori pada dataset, hanya empat yang tampil, sedangkan Sejarah dan Prosa tersaring karena tidak memiliki satu buku pun. Kategori Ilmu Komputer tetap lolos meskipun bukunya hanya satu, yaitu Kamus Istilah Komputer yang memang tercatat langsung pada kategori induk tersebut. Sifat penghentian dini pada kecocokan pertama membuat EXISTS sering dipilih untuk pertanyaan keberadaan semacam ini.

Nol baris atau banyak baris#

Perilaku subquery skalar ditetapkan oleh dokumentasi SQLite dan perlu dipahami dari dua sisi. Apabila subquery tidak mengembalikan baris apa pun, nilainya menjadi NULL, dan perbandingan apa pun terhadap NULL bernilai tidak diketahui sehingga tidak ada baris yang lolos. Peragakan efeknya dengan menghitung baris yang cocok, sehingga hasil nol tetap terlihat sebagai keluaran dan tidak dikira query yang gagal.

sql Menghitung baris yang cocok dengan subquery kosong 
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 COUNT(*) AS ketemu
FROM buku
WHERE tahun = (SELECT MAX(tahun) FROM buku WHERE id_kategori = 7);
text Keluaran 
0

Subquery di dalamnya mencari buku terbaru pada kategori Prosa yang memang tidak memiliki buku, sehingga hasilnya NULL dan pencocokan tahun terhadap NULL gagal untuk seluruh baris. Kasus sebaliknya, yaitu subquery skalar yang mengembalikan lebih dari satu baris, tidak memunculkan error pada SQLite. Dokumentasi menetapkan bahwa nilai subquery dalam ekspresi skalar diambil dari baris pertama hasil SELECT di dalamnya.

sql Subquery skalar yang mengembalikan tiga baris 
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 judul, tahun
FROM buku
WHERE tahun = (SELECT tahun FROM buku WHERE id_kategori = 4);
text Keluaran 
Dasar Algoritma|2018

Hanya Dasar Algoritma yang tampil, padahal subquery mengembalikan tiga nilai tahun sekaligus, yaitu 2018, 2021, dan 2019, sehingga Struktur Data Terapan dan Analisis Kompleksitas diam-diam diabaikan. Keluaran seperti ini rawan menyesatkan karena tampak sah tetapi bergantung pada urutan penyimpanan baris, dan sejumlah perangkat lunak basis data lain bahkan menolak bentuk ini dengan error.

Ringkasan#

  • Subquery skalar menggantikan satu nilai di WHERE maupun di daftar SELECT, dan bila tidak berkorelasi cukup dievaluasi satu kali.
  • IN menguji keanggotaan terhadap daftar hasil subquery, sedangkan EXISTS menguji keberadaan baris dengan korelasi ke baris luar.
  • Subquery skalar yang tidak mengembalikan baris bernilai NULL sehingga tidak ada baris yang lolos.
  • Subquery skalar yang mengembalikan banyak baris memakai baris pertamanya, sehingga hasilnya perlu dituntun dengan agregat atau LIMIT 1.

Referensi#