Subquery SQLite: SELECT Bersarang di WHERE, FROM, dan SELECT

Subquery Adalah SELECT di Dalam SELECT

Subquery persis seperti namanya: pernyataan SELECT yang diselipkan di dalam pernyataan lain dan dibungkus tanda kurung. SQLite menjalankan query di dalam, mengambil hasilnya, lalu memberikannya ke query di luar.

Siapkan contoh kecil yang bisa kita pakai berulang kali:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders;

Lima pesanan, empat pelanggan, dua di antaranya belum memesan apa pun. Kita akan memakai data ini sepanjang halaman.

Subquery di WHERE: Memfilter dengan Daftar

Bentuk yang paling umum: ambil daftar id di query dalam, lalu filter query luar berdasarkan daftar itu.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

Query dalam menghasilkan setiap customer_id yang muncul di orders. Query luar hanya menyimpan pelanggan yang id-nya ada di daftar itu. Cleo, Boris, dan Ada muncul; Dmitri (tanpa pesanan) tidak.

IN (SELECT ...) adalah pola andalan untuk "baris di A yang punya pasangan di B." Bacalah dalam hati sebagai "di mana nilai kolom ini adalah salah satu nilai yang dikembalikan query dalam."

NOT IN: Waspadai NULL

Pertanyaan kebalikannya, "pelanggan mana yang belum memesan?", cukup berbeda satu baris:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Di sini berhasil. Tapi NOT IN punya sisi tajam: jika subquery pernah mengembalikan NULL, seluruh NOT IN menjadi NULL (yang bukan TRUE), dan kamu mendapat nol baris. Mengejutkan, dan tanpa peringatan.

Kebiasaan yang aman saat memakai NOT IN terhadap kolom yang mungkin berisi NULL:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Atau pakai NOT EXISTS, yang sama sekali tidak punya masalah ini. Kita akan membahasnya nanti.

Scalar Subquery: Satu Baris, Satu Kolom

Scalar subquery mengembalikan satu nilai, yaitu satu baris dan satu kolom, dan kamu bisa memakainya di mana pun sebuah nilai diharapkan.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name, total
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE total = (SELECT MAX(total) FROM orders);

Query dalam SELECT MAX(total) FROM orders mengembalikan 200. Query luar lalu memfilter pesanan yang cocok dengan nilai itu. Berguna setiap kali kamu perlu membandingkan dengan hasil agregat.

Kamu juga bisa memakai scalar subquery di daftar SELECT untuk menempelkan nilai hasil perhitungan ke setiap baris:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT
    name,
    (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count
FROM customers;

Setiap baris customers menjalankan query dalam satu kali, dengan customers.id dimasukkan ke dalamnya. Itulah correlated subquery, yang dibahas lebih lanjut di bawah. Untuk kasus "satu angka per baris" seperti ini, LEFT JOIN dengan GROUP BY biasanya lebih cepat, tapi bentuk scalar ini enak sekali dibaca.

EXISTS: Cukup Periksa Apakah Ada yang Cocok

EXISTS adalah sepupu IN yang lebih kalem. Ia tidak peduli pada nilai, hanya memeriksa apakah subquery mengembalikan baris apa pun. Biasanya kamu menulis SELECT 1 di dalamnya, karena kolomnya tidak penting.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.total > 100
);

Query ini mencari pelanggan yang pernah membuat setidaknya satu pesanan di atas 100. Query dalam merujuk ke c.id dari query luar, dan itulah yang membuatnya correlated. SQLite berhenti memindai tabel dalam begitu menemukan kecocokan, itulah sebabnya EXISTS sering lebih cepat daripada IN untuk pertanyaan "apakah baris ini punya baris terkait?".

Negasinya, NOT EXISTS, adalah cara yang aman terhadap NULL untuk menanyakan "tidak ada baris terkait":

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Subquery di FROM: Derived Table

Subquery bisa menggantikan tabel di mana saja, termasuk di klausa FROM. Query dalam menjadi "derived table" sementara yang diberi nama, yang bisa kamu join, filter, atau agregasi.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT country, AVG(customer_total) AS avg_per_customer
FROM (
    SELECT c.country, c.id, SUM(o.total) AS customer_total
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id
) AS per_customer
GROUP BY country;

Query dalam menghitung total per pelanggan. Query luar merata-ratakan total itu per negara. Agregasi dua tahap seperti ini memang kegunaan derived table, saat kamu tidak bisa mengerjakan semuanya dalam satu GROUP BY.

Alias AS per_customer wajib ada: setiap derived table butuh nama.

Correlated Subquery: Dijalankan per Baris Luar

Subquery disebut correlated saat merujuk ke kolom dari query luar. SQLite harus mengevaluasi ulang query dalam untuk setiap baris luar, yang fleksibel tapi bisa jadi mahal.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name,
       (SELECT MAX(total)
        FROM orders
        WHERE customer_id = customers.id) AS biggest_order
FROM customers;

Untuk setiap pelanggan, cari pesanan terbesarnya. Query dalam bergantung pada customers.id, jadi ia dijalankan sekali per pelanggan. Pelanggan tanpa pesanan mendapat NULL, dan memang itulah yang kamu inginkan.

Correlated subquery paling cocok untuk "untuk setiap baris di A, hitung sesuatu dari B." Jika tabelnya kecil atau pencariannya memakai index, tidak masalah. Pada tabel besar tanpa index pendukung, ukur dulu performanya sebelum dirilis: JOIN dengan GROUP BY sering lebih cepat.

Subquery vs JOIN: Pilih yang Mana?

Dua query ini menjawab pertanyaan yang sama:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Subquery
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

-- JOIN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

Keduanya mengembalikan baris yang sama. Optimizer SQLite sering menulis ulang satu bentuk menjadi bentuk lainnya secara internal. Pilih berdasarkan kemudahan dibaca:

• Pakai subquery saat kamu hanya perlu memfilter, dan tidak ingin kolom dari tabel dalam mengotori hasilnya.
• Pakai JOIN saat hasilnya butuh kolom dari kedua tabel.
• Pakai EXISTS saat kamu bertanya "apakah ada setidaknya satu baris terkait?". Bentuk ini lebih jelas dan menghindari jebakan NULL pada IN/NOT IN.

Kalau ragu, tulis versi yang menjelaskan dirinya sendiri saat dibaca keras-keras.

Jebakan Umum: Subquery yang Mengembalikan Banyak Baris

Subquery yang dipakai dengan = harus mengembalikan paling banyak satu baris. Jika mengembalikan lebih, SQLite memilih salah satunya (praktis secara acak) dan kamu diam-diam mendapat hasil yang salah, tanpa error.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Risky: what if more than one customer is from the UK?
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'UK');

Pakai IN saat query dalam bisa mengembalikan banyak baris:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'UK');

Jika kamu mengharapkan tepat satu baris dan ingin memastikannya, tambahkan LIMIT 1 dan ORDER BY supaya pilihannya setidaknya deterministik. Lebih baik lagi: tulis query-nya sehingga satu baris dijamin oleh datanya (filter pada kolom yang unik).

Berikutnya: Common Table Expression

Subquery di FROM cepat jadi sulit dikelola, terutama saat kamu butuh derived table yang sama dua kali, atau saat sarangnya sampai tiga tingkat. Common Table Expression (WITH ... AS (...)) memungkinkanmu memberi nama sebuah subquery di awal lalu merujuknya dengan nama itu di sisa pernyataan. Itu halaman berikutnya.