KAL
Menu

Subquery

Subquery pada WHERE dan SELECT: IN, EXISTS, dan subquery skalar beserta batasannya.

Daftar isi

Subquery adalah query SELECT yang ditulis di dalam query lain, dan hasilnya berupa satu nilai, satu baris, atau sekumpulan baris yang dipakai query luarnya. Topik ini membahas tiga tempat penulisan yang paling umum, yaitu subquery skalar pada WHERE, penyaringan dengan IN dan EXISTS, serta subquery skalar pada daftar SELECT. Batasan yang diberlakukan server juga diperagakan lewat error aslinya. Contoh memakai basis data contoh sakila sehingga seluruh keluaran dapat direproduksi.

Subquery skalar pada WHERE#

Subquery skalar mengembalikan tepat satu nilai, dan bentuk ini paling sering dipakai untuk membandingkan baris terhadap nilai agregat. Subquery dijalankan lebih dahulu, lalu nilainya dipakai pada kondisi WHERE query luar. Contoh klasiknya adalah mencari film yang lebih panjang dari rata-rata panjang seluruh film.

bash 
# rata-rata panjang film, lalu film yang melewatinya
mysql -t -u root sakila -e "SELECT ROUND(AVG(length), 3) AS rata_panjang FROM film;"
mysql -t -u root sakila -e "SELECT title, length FROM film WHERE length > (SELECT AVG(length) FROM film) ORDER BY film_id LIMIT 5;"
text Keluaran 
+--------------+
| rata_panjang |
+--------------+
|      115.272 |
+--------------+
+------------------+--------+
| title            | length |
+------------------+--------+
| AFFAIR PREJUDICE |    117 |
| AFRICAN EGG      |    130 |
| AGENT TRUMAN     |    169 |
| ALAMO VIDEOTAPE  |    126 |
| ALASKA PHANTOM   |    136 |
+------------------+--------+

Query pertama menghitung rata-rata panjang film, yaitu 115.272 menit. Query kedua menyertakan subquery yang sama di dalam kondisi length > (...) dan mengembalikan film yang melewatinya. Sebutan skalar merujuk pada satu nilai tunggal, bukan kolom ataupun baris. Penulisan seperti ini lebih andal daripada menyalin angka rata-rata secara manual, karena nilainya selalu dihitung ulang dari data terkini.

IN dengan subquery#

Operator IN menerima sekumpulan nilai, dan kumpulan itu boleh berasal dari subquery. Query luar menyaring baris yang kolomnya ada pada kumpulan hasil subquery. Pola ini cocok untuk hubungan yang melalui tabel penghubung, misalnya mencari film dalam satu kategori pada sakila.

bash 
# kategori Action beserta sebagian filmnya
mysql -t -u root sakila -e "SELECT category_id, name FROM category WHERE name = 'Action';"
mysql -t -u root sakila -e "SELECT title, rating FROM film WHERE film_id IN (SELECT film_id FROM film_category WHERE category_id = 1) ORDER BY film_id LIMIT 4;"
text Keluaran 
+-------------+--------+
| category_id | name   |
+-------------+--------+
|           1 | Action |
+-------------+--------+
+--------------------+--------+
| title              | rating |
+--------------------+--------+
| AMADEUS HOLY       | PG     |
| AMERICAN CIRCUS    | R      |
| ANTITRUST TOMATOES | NC-17  |
| ARK RIDGEMONT      | NC-17  |
+--------------------+--------+

Query pertama memastikan kategori Action memiliki category_id 1. Subquery pada query kedua mengambil seluruh film_id dari film_category yang tergolong kategori tersebut, lalu query luar menampilkan filmnya. Hanya empat baris pertama yang ditampilkan dengan LIMIT agar keluaran tetap ringkas, sedangkan kategori Action sebenarnya memuat 64 film. Kolom yang dibandingkan dengan IN harus satu kolom yang sejajar dengan daftar hasil subquery.

EXISTS untuk subquery berkorelasi#

Subquery berkorelasi merujuk kolom dari query luar, sehingga diperiksa ulang untuk setiap baris kandidat. Bentuk yang tepat untuk pola ini adalah EXISTS, yang hanya menanyakan apakah subquery menghasilkan setidaknya satu baris. Nilai di dalam subquery tidak dipakai, dan konvensi yang lazim adalah menulis SELECT 1.

bash 
# aktor yang membintangi film ALONE TRIP
mysql -t -u root sakila -e "SELECT a.first_name, a.last_name FROM actor a WHERE EXISTS (SELECT 1 FROM film_actor fa JOIN film f ON f.film_id = fa.film_id WHERE fa.actor_id = a.actor_id AND f.title = 'ALONE TRIP') ORDER BY a.actor_id;"
text Keluaran 
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| ED         | CHASE     |
| KARL       | BERRY     |
| UMA        | WOOD      |
| WOODY      | JOLIE     |
| SPENCER    | DEPP      |
| CHRIS      | DEPP      |
| LAURENCE   | BULLOCK   |
| RENEE      | BALL      |
+------------+-----------+

Keluaran memuat delapan aktor yang membintangi film ALONE TRIP. Subquery bagian dalam menggabungkan film_actor dengan film untuk memeriksa satu judul, dan kondisi fa.actor_id = a.actor_id menjadikannya berkorelasi. Penulisan dengan EXISTS menyatakan maksud pencarian keberadaan secara langsung, sehingga lebih mudah dibaca daripada membentuk daftar terlebih dahulu lalu memakai IN.

Subquery skalar pada SELECT#

Subquery skalar juga boleh berada pada daftar SELECT, dan biasanya dipakai untuk melampirkan nilai agregat pada setiap baris hasil. Subquery dievaluasi untuk setiap baris luar, dan korelasi melalui kolom luar membuat nilainya berbeda per baris. Contoh berikut menambahkan jumlah film pada setiap bahasa.

bash 
# jumlah film untuk setiap bahasa
mysql -t -u root sakila -e "SELECT l.name, (SELECT COUNT(*) FROM film f WHERE f.language_id = l.language_id) AS jumlah_film FROM language l ORDER BY l.language_id;"
text Keluaran 
+----------+-------------+
| name     | jumlah_film |
+----------+-------------+
| English  |        1000 |
| Italian  |           0 |
| Japanese |           0 |
| Mandarin |           0 |
| French   |           0 |
| German   |           0 |
+----------+-------------+

Bahasa Inggris membawa angka 1000, sedangkan lima bahasa lain membawa nol. Hasil yang sama juga dapat diperoleh dengan LEFT JOIN dan COUNT, sehingga pilihan di antara keduanya menyangkut keterbacaan query. Subquery pada SELECT yang tidak berkorelasi dievaluasi sekali saja, tetapi bentuk berkorelasi pada tabel besar berpotensi lambat karena berulang untuk setiap baris.

IN versus EXISTS#

Kedua bentuk menyatakan pencarian keberadaan, dan pada MySQL 8.0 keduanya sering ditransformasi optimizer menjadi rencana semi-join yang sama. Perbandingan berikut menghitung bahasa yang memiliki film dengan kedua cara.

bash 
# jumlah bahasa yang dipakai film, dua cara menghitung
mysql -t -u root sakila -e "SELECT COUNT(*) AS via_in FROM language l WHERE l.language_id IN (SELECT language_id FROM film);"
mysql -t -u root sakila -e "SELECT COUNT(*) AS via_exists FROM language l WHERE EXISTS (SELECT 1 FROM film f WHERE f.language_id = l.language_id);"
text Keluaran 
+--------+
| via_in |
+--------+
|      1 |
+--------+
+------------+
| via_exists |
+------------+
|          1 |
+------------+

Keduanya mengembalikan 1, yaitu hanya bahasa Inggris yang dipakai film. Panduan praktisnya, IN enak dipakai ketika daftar berasal dari subquery sederhana yang berdiri sendiri, sedangkan EXISTS lebih tepat ketika korelasi ke baris luar diperlukan. Selain itu, EXISTS tetap aman ketika kolom yang diperiksa dapat berisi NULL, persoalan yang diperagakan pada bagian Kesalahan umum.

Batasan subquery#

Server memberlakukan sejumlah batasan pada subquery, dan dua batasan yang paling sering berpapasan adalah konteks skalar serta dukungan LIMIT. Subquery yang dipakai sebagai nilai wajib mengembalikan paling banyak satu baris, sedangkan LIMIT belum didukung di dalam IN, ALL, ANY, dan SOME. Keduanya diperagakan lewat error asli berikut.

bash 
# LIMIT di dalam subquery IN
mysql -t -u root sakila -e "SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film ORDER BY film_id LIMIT 5) LIMIT 3;"
text Keluaran 
ERROR 1235 (42000) at line 1: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
bash 
# subquery skalar yang mengembalikan banyak baris
mysql -t -u root sakila -e "SELECT title FROM film WHERE language_id = (SELECT language_id FROM film WHERE rating = 'R') LIMIT 3;"
text Keluaran 
ERROR 1242 (21000) at line 1: Subquery returns more than 1 row

Pesan error pertama menyatakan dukungan LIMIT pada subquery tersebut belum ada, dan jalan keluarnya adalah membalik urutan kerjanya, misalnya menyimpan hasil pemilihan baris pada tabel sementara terlebih dahulu. Pesan error kedua muncul karena subquery skalar mengembalikan banyak baris sekaligus. Perbaikannya adalah memastikan subquery mengembalikan satu baris, misalnya dengan agregat seperti MAX atau dengan kondisi pada kolom primary key.

Kesalahan umum#

Jebakan paling licik ada pada NOT IN yang bertemu NULL. Apabila subquery mengembalikan satu saja nilai NULL, seluruh hasil NOT IN menjadi kosong karena perbandingan terhadap NULL tidak pernah menghasilkan benar. Tabel film memuat kolom original_language_id yang seluruh 1000 barisnya NULL, dan perbandingan berikut memperlihatkan akibatnya.

bash 
# NOT IN tanpa NULL dibandingkan dengan NOT IN yang bertemu NULL
mysql -t -u root sakila -e "SELECT COUNT(*) AS harapan FROM language WHERE language_id NOT IN (SELECT language_id FROM film);"
mysql -t -u root sakila -e "SELECT COUNT(*) AS kenyataan FROM language WHERE language_id NOT IN (SELECT original_language_id FROM film);"
text Keluaran 
+---------+
| harapan |
+---------+
|       5 |
+---------+
+-----------+
| kenyataan |
+-----------+
|         0 |
+-----------+

Query pertama aman karena kolom language_id tidak pernah NULL, sehingga menghasilkan 5, yaitu lima bahasa yang tidak pernah dipakai film mana pun. Query kedua memakai pola yang sama pada original_language_id, tetapi kolom itu berisi NULL, sehingga keluarannya 0, bukan daftar bahasa yang diharapkan. Perbaikan untuk kasus yang mengandung NULL adalah menambahkan WHERE kolom IS NOT NULL pada subquery atau beralih ke NOT EXISTS.

Ringkasan#

  • Subquery skalar mengembalikan satu nilai dan tepat dipakai untuk perbandingan agregat pada WHERE maupun daftar SELECT.
  • IN menyaring baris yang terdapat pada kumpulan hasil subquery, sedangkan EXISTS memeriksa keberadaan baris pada subquery berkorelasi.
  • MySQL 8.0 menyetarakan banyak bentuk IN dan EXISTS melalui transformasi semi-join, sehingga perbedaannya lebih ke arah keterbacaan.
  • Subquery skalar wajib mengembalikan paling banyak satu baris, LIMIT belum didukung di dalam IN, dan NOT IN yang bertemu NULL menghasilkan himpunan kosong.

Referensi#