Beberapa pertanyaan memerlukan dua langkah untuk dijawab. “Pesanan mana yang di atas rata-rata?” mengharuskan Anda menemukan rata-ratanya terlebih dahulu, lalu membandingkan setiap pesanan dengannya. Subkueri SQL memungkinkan Anda menulis kedua langkah sebagai satu pernyataan.

Tutorial ini mencakup setiap jenis menggunakan dua tabel kecil. Kami menjalankan kueri ini di SQLite 3.50, dan hasil yang ditampilkan berasal dari proses tersebut. Sintaks subkueri standar didukung secara luas, dan kami menunjukkan perbedaan database.

Apa itu subkueri?

Subkueri, juga disebut kueri dalam atau kueri bersarang, adalah pernyataan SELECT yang ditempatkan di dalam pernyataan lain, dalam tanda kurung bulat. Secara konseptual, kueri dalam menghasilkan hasil yang kemudian digunakan oleh kueri luar.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Tabel sampel

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Perhatikan bahwa Dev tidak memiliki perintah. Detail tersebut penting untuk beberapa contoh di bawah.

Sekilas tentang jenis subquery

Subkueri skalar

Subkueri skalar mengembalikan tepat satu nilai, sehingga Anda dapat menggunakannya di mana pun nilai tunggal tersebut masuk akal. Ini query dari sebelumnya, beserta hasilnya. Rata-rata semua pesanan adalah 980:

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

Anda tidak dapat menulis WHERE total > AVG(total), karena agregat tidak diperbolehkan langsung di WHERE. Subkueri menyiasatinya dengan menghitung nilainya secara terpisah.

Bagaimana jika subkueri “skalar” mengembalikan beberapa baris? Sebagian besar database, termasuk PostgreSQL, MySQL dan SQL Server, memunculkan kesalahan. SQLite tidak. Dalam pengujian kami, secara diam-diam hanya menggunakan baris pertama, yang dapat menyembunyikan bug sebenarnya, jadi jangan bergantung pada perilaku ini.

DALAM dan BUKAN DALAM

Saat kueri dalam mengembalikan daftar, gunakan IN. Ini menemukan pelanggan dengan setidaknya satu pesanan lebih dari 1000:

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

-- Asha
-- Chitra

NOT IN melakukan sebaliknya, menemukan pelanggan yang belum pernah memesan:

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

-- Dev

Itu berfungsi di sini, tetapi NOT IN memiliki kasus tepi yang berbahaya, yang kami bahas di bawah.

Subkueri di FROM

Subquery di FROM bertindak sebagai tabel sementara, sering disebut tabel turunan. Hal ini berguna ketika Anda perlu menggabungkan dua kali, seperti dalam kueri untuk pembelanjaan rata-rata per pelanggan di setiap kota:

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

Kueri bagian dalam menjumlahkan setiap pelanggan, dan kueri bagian luar menghitung rata-rata total tersebut. Memberi alias pada tabel turunan adalah kebiasaan yang baik, karena beberapa database memerlukannya. Jika GROUP BY baru bagi Anda, lihat SQL GROUP BY dan HAVING.

Subkueri di SELECT

Subkueri skalar juga dapat menambahkan kolom terhitung. Yang ini menghitung pesanan per pelanggan:

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

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev muncul dengan nol. Gabungan dalam akan menghilangkan Dev sepenuhnya, karena Dev tidak memiliki baris yang cocok. Anda bisa mendapatkan hasil yang sama dengan LEFT JOIN dan GROUP BY, yang kami jelaskan di penjelasan gabungan SQL.

Subkueri yang berkorelasi

Contoh sebelumnya juga berkorelasi, karena kueri dalam merujuk ke c.id dari kueri luar. Subkueri yang tidak berkorelasi dapat berjalan sendiri. Yang berkorelasi tidak bisa, karena bergantung pada nilai dari baris terluar saat ini.

Ini menemukan pesanan di atas rata-rata pelanggan yang sama:

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

Berikut cara membacanya. Untuk pesanan 101, kueri dalam menghitung rata-rata pesanan pelanggan 1 (1200 dan 800), yang menghasilkan 1000. Karena 1200 lebih besar, baris tersebut dipertahankan. Untuk order 102 berlaku rata-rata yang sama, namun 800 tidak lebih besar sehingga dihilangkan. Pesanan tunggal Pelanggan 2 sama dengan rata-rata pesanannya sendiri, jadi pesanan tersebut juga dibatalkan.

“Sekali per baris” menjelaskan logikanya, dan pengoptimal kueri sering kali menulis ulang logika ini menjadi sesuatu yang jauh lebih efisien. Namun, subquery yang berkorelasi lambat layak untuk diselidiki. Indeks pada kolom dalam, di sini orders.customer_id, dapat membuat perbedaan besar. Lihat penjelasan indeks SQL.

ADA dan TIDAK ADA

EXISTS menanyakan pertanyaan ya atau tidak: apakah kueri dalam mengembalikan setidaknya satu baris? Itu bisa berhenti di pertandingan pertama, dan apa yang Anda pilih di dalamnya tidak masalah, itulah sebabnya orang sering menulis SELECT 1.

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

-- Asha, Ben, Chitra

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

-- Dev

Perangkap NOT IN NULL

Ini adalah salah satu bug subkueri yang paling umum. Kami membuat tabel dengan satu ID asli dan satu NULL, lalu menjalankan kedua formulir tersebut:

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

Mengapa yang pertama tidak menghasilkan apa-apa? id NOT IN (1, NULL) berarti id <> 1 AND id <> NULL. Perbandingan apa pun dengan NULL tidak diketahui dan bukan benar, sehingga seluruh kondisi tidak akan pernah benar. Tidak ada pesan kesalahan dan tidak ada peringatan, hanya hasil kosong yang terlihat masuk akal.

Subkueri vs GABUNG vs CTE

Banyak pertanyaan dapat ditulis lebih dari satu cara. Sebelumnya kami menemukan pelanggan dengan pesanan besar menggunakan IN. Berikut hasil yang sama dengan join:

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

Perhatikan DISTINCT. Gabungan dapat menghasilkan duplikat jika pelanggan memiliki beberapa kecocokan, sedangkan IN dan EXISTS tidak bisa.

Kinerja bervariasi menurut database dan data, dan pengoptimal modern sering kali menghasilkan rencana serupa. Jadi pilihlah bentuk yang paling jelas terlebih dahulu, lalu centang EXPLAIN apakah ternyata lambat.

Kesalahan umum

• Menggunakan = dengan hasil multi-baris. Gunakan IN, atau pastikan kueri dalam mengembalikan satu baris.
• Menggunakan NOT IN pada kolom yang dapat dibatalkan. Anda dapat kehilangan setiap baris secara diam-diam.
• Menggunakan alias yang tidak jelas. Dalam kueri yang berkorelasi, memenuhi syarat setiap kolom, atau kolom tersebut mungkin terikat ke tabel yang salah.
• Sarang yang dalam. Tiga level sulit dibaca, jadi konversikan ke CTE.
• Menambahkan ORDER BY di dalam. Biasanya tidak berpengaruh pada hasil luar, dan beberapa database menolaknya.

Penutup

Subkueri adalah cara alami untuk mengungkapkan pertanyaan dua langkah. Mulailah dengan subkueri skalar dan IN, lalu praktikkan subkueri yang berkorelasi dengan memikirkan satu baris terluar pada satu waktu. Dan ingat NOT EXISTS kapan pun NULL memungkinkan. Untuk berlatih, tulis satu pertanyaan dalam tiga cara, dengan subkueri, gabungan, dan CTE, lalu bandingkan hasil dan keterbacaannya.

Pertanyaan yang sering diajukan

Teruslah belajar