MySQL SubQuery dengan Contoh

Apa itu SubQuery dalam SQL?

A subkueri SELECT adalah kueri SELECT yang terdapat di dalam kueri lain. Kueri SELECT bagian dalam biasanya digunakan untuk menentukan hasil dari kueri SELECT bagian luar, sehingga basis data mengevaluasi pernyataan bagian dalam terlebih dahulu dan kemudian meneruskan hasilnya ke atas.

Kueri internal disebut kueri internal atau kueri bersarang, dan pernyataan yang memuatnya disebut kueri luarMari kita lihat sintaks subkueri.

Diagram di atas menunjukkan bentuk umum pernyataan tersebut: SELECT terluar menyediakan kolom yang ingin Anda lihat, dan SELECT dalam yang diapit tanda kurung menyediakan nilai atau daftar nilai yang dibandingkan oleh klausa WHERE.

Mengapa Menggunakan Subkueri?

Sebelum membahas berbagai jenisnya, ada baiknya mengetahui kapan subquery diperlukan dalam sebuah pernyataan.

Subkueri menjawab pertanyaan yang nilai filternya tidak diketahui sebelumnya. Nilai tersebut harus dihitung dari data itu sendiri. Keluhan pelanggan yang umum di Perpustakaan Video MyFlix adalah sedikitnya jumlah judul film, dan manajemen ingin membeli film untuk kategori yang memiliki jumlah judul paling sedikit. Tidak ada yang tahu kategori mana itu sampai basis data diminta, jadi nilainya harus dihitung terlebih dahulu dan kemudian digunakan sebagai filter.

Subkueri berada ditracHal ini penting karena tiga alasan praktis:

• dibaca: Setiap bagian dari logika tersebut berada dalam blok yang diapit tanda kurung masing-masing, sehingga pernyataan tersebut terbaca sebagai serangkaian pertanyaan kecil daripada satu ekspresi yang kompleks.
• Isolasi: Sebuah query internal dapat dijalankan secara terpisah untuk memastikan bahwa query tersebut mengembalikan nilai yang diharapkan, sehingga pengujian dan debugging menjadi jauh lebih mudah.
• Fleksibilitas: Pola yang sama berlaku pada klausa WHERE, HAVING, SELECT, dan FROM, serta di dalam pernyataan INSERT, UPDATE, dan DELETE.

Komprominya adalah kecepatan, yang akan dibahas dalam perbandingan JOIN di bagian selanjutnya dari artikel ini.

Jenis-jenis Subkueri dalam MySQL

MySQL Mendukung tiga jenis subkueri, dan jenisnya ditentukan oleh bentuk hasil yang dikembalikan oleh kueri dalam. Setiap jenis dijelaskan di bawah ini dengan contoh yang berfungsi pada basis data myflixdb.

1) Subkueri Skalar

A subkueri skalar Fungsi ini mengembalikan tepat satu baris dan satu kolom, yang berarti mengembalikan satu nilai tunggal. Karena hasilnya adalah nilai tunggal, nilai tersebut dapat digunakan di mana pun nilai literal diperbolehkan. Kembali ke masalah MyFlix di atas, Anda dapat menggunakan kueri seperti ini:

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

Ini memberikan hasil:

Mari kita lihat bagaimana kueri ini bekerja.

Seperti yang ditunjukkan pada diagram eksekusi, MySQL lari pertama SELECT MIN(category_id) FROM movies, menerima satu nilai, dan baru kemudian menjalankan kueri luar dengan nilai tersebut. Karena hanya satu nilai yang dikembalikan, operator yang diizinkan adalah himpunan perbandingan standar: =, <> (Atau !=), >, >=, <, dan <=.

💡 Kiat: Jika subkueri ditempatkan setelah = mengembalikan lebih dari satu baris, MySQL menimbulkan kesalahan 1242, Subkueri mengembalikan lebih dari 1 baris. Ganti operator ke INatau memperketat klausa WHERE bagian dalam.

2) Subkueri Baris

A subkueri baris Selain itu, kueri ini juga mengembalikan satu baris, tetapi baris tersebut mungkin berisi lebih dari satu kolom. Oleh karena itu, kueri luar membandingkan satu baris nilai dengan konstruktor baris, bukan dengan satu nilai tunggal.

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

Operator yang diperbolehkan adalah operator perbandingan yang sama seperti yang tercantum di atas, yang diterapkan pada seluruh baris sekaligus.

3) Subkueri Tabel

A subkueri tabel mengembalikan beberapa baris, dan seringkali beberapa kolom, sehingga kueri luar harus menggunakan operator himpunan seperti IN, NOT IN, ANY, ALL, atau EXISTS.

Misalkan Anda ingin mendapatkan nama dan nomor telepon anggota yang telah menyewa film dan belum mengembalikannya, sehingga Anda dapat menghubungi mereka untuk memberikan pengingat. Anda dapat menggunakan kueri seperti ini:

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

Mari kita lihat bagaimana kueri ini bekerja.

Dalam kasus ini, kueri internal mengembalikan lebih dari satu hasil, sehingga daftar nomor keanggotaan diserahkan ke IN operator dan setiap anggota yang cocok akan dikembalikan.

Menyusun Subkueri Beberapa Tingkat Kedalaman

Sejauh ini Anda telah melihat dua level. Sebuah subkueri juga dapat berisi subkueri lain, yang menghasilkan pernyataan bersarang tiga tingkat.

Misalkan manajemen ingin memberi penghargaan kepada anggota dengan pembayaran tertinggi. Kita dapat menjalankan kueri seperti ini:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

Kueri terdalam menemukan pembayaran terbesar, kueri tengah mengubah jumlah tersebut menjadi nomor keanggotaan, dan kueri terluar mengubah nomor keanggotaan menjadi nama. Kueri di atas memberikan hasil sebagai berikut:

Cara Menggunakan Subquery dengan INSERT, UPDATE, dan DELETE

Subkueri tidak terbatas pada pernyataan SELECT. Pola tanda kurung yang sama juga berlaku di dalam pernyataan modifikasi data, yang memungkinkan seluruh rangkaian baris diubah dalam satu kali proses tanpa membuat tabel sementara.

INSERT dengan subquery. Subkueri dapat menyediakan baris yang disisipkan, yang menyalin data dari satu tabel ke tabel lain. Daftar kolom SELECT harus sesuai dengan daftar kolom INSERT.

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

Perbarui dengan subkueri. Di sini, kueri internal menentukan baris mana yang akan diproses. Contoh di bawah ini menandai setiap anggota yang tunggakan sewanya masih berlaku.

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

Hapus dengan subkueri. Ide yang sama digunakan untuk menghapus baris yang memenuhi suatu kondisi yang terdapat dalam tabel kedua.

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️ Peringatan: MySQL Tidak diperbolehkan adanya pernyataan untuk memodifikasi tabel dan memilih dari tabel yang sama di dalam subkueri pada klausa FROM. Jika muncul kesalahan 1093, bungkus kueri dalam tersebut dalam tabel turunan, misalnya SELECT * FROM (SELECT ...) AS t, Sehingga MySQL Mewujudkan hasilnya sebelum perubahan diterapkan. Sebaiknya jalankan juga perintah SELECT bagian dalam terlebih dahulu secara terpisah, dan konfirmasikan jumlah barisnya, sebelum menjalankan perintah SELECT lainnya. UPDATE atau DELETE dalam produksi.

Subkueri vs. Gabungan

Baik subquery maupun JOIN dapat menggabungkan informasi dari lebih dari satu tabel, jadi pertanyaan yang wajar adalah mana yang harus digunakan.

Jika dibandingkan dengan join, sub-query lebih mudah digunakan dan dibaca. Sub-query tidak serumit Bergabung, dan oleh karena itu sering digunakan oleh Pemula SQL.

Namun, sub-query memiliki masalah performa. Menggunakan join sebagai pengganti sub-query terkadang dapat memberikan peningkatan performa hingga 500 kali lipat, karena optimizer dapat menyelesaikan join dalam satu kali proses alih-alih mengevaluasi pernyataan di dalamnya berulang kali.

Jika diberi pilihan, disarankan untuk menggunakan GABUNG pada sub kueri. Sub-query sebaiknya hanya digunakan sebagai solusi cadangan ketika Anda tidak dapat menggunakan operasi JOIN untuk mencapai tujuan di atas.

Subkueri juga mudah dipecah menjadi komponen logis tunggal, yang sangat berguna ketika pengujian dan men-debug kueri.

Pertanyaan Umum Demo Slot