Menggunakan subquery apa yang akan mengembalikan syntax error?

Anda barangkali hanya sering menggunakan statement SQL SELECT secara tunggal, maksudnya adalah statement SQL SELECT yang Anda gunakan hanyalah berbentuk SELECT … FROM … [WHERE …].

Perlu Anda tahu bahwa, di dalam statement SQL bisa terdapat subquery lagi atau dengan kata lain ‘query dalam query’ atau disebut juga ‘nested query’. Hal ini tidak hanya terjadi pada statement SELECT, namun dapat pula terjadi pada statement UPDATE dan DELETE.

Seperti apa bentuknya serta contohnya? Perhatikan contoh-contoh kasus berikut ini.

Untuk studi kasus, lagi-lagi kita ambil sampel yang terkait dengan nilai matakuliah mahasiswa, karena merupakan sampel yang paling mudah dalam pemahamannya. Berikut ini adalah struktur tabel yang digunakan beserta recordnya.

CREATE TABLE mhs ( nim varchar(5), namaMhs varchar(30), PRIMARY KEY(nim) );

INSERT INTO mhs VALUES ('001', 'Joko'), ('002', 'Amir'), ('003', 'Budi');

CREATE TABLE mk ( kodeMK varchar(5), namaMK varchar(20), sks int(11), PRIMARY KEY(kodeMK) );

INSERT INTO mk VALUES ('A01', 'Kalkulus', 3), ('A02', 'Geometri', 2), ('A03', 'Aljabar', 3);

CREATE TABLE ambilmk ( nim varchar(5), kodeMK varchar(5), nilai int(11), PRIMARY KEY(nim, kodeMK) );

INSERT INTO ambilmk VALUES ('001', 'A01', 3), ('001', 'A02', 4), ('001', 'A03', 2), ('002', 'A02', 3), ('002', 'A03', 2), ('003', 'A01', 4), ('003', 'A03', 3);

Nah selanjutnya misalkan ada pertanyaan-pertanyaan sebagai berikut ini:

1. Tampilkan nama mahasiswa dan nilai matakuliah yang memiliki nilai tertinggi dalam matakuliah ‘A02’.
2. Dalam perkuliahan dengan kode ‘A03’, siapakah mahasiswa (nim dan nama) yang memiliki nilai di atas rata-rata nilai dari semua mahasiswa yang mengambil matakuliah tersebut?
3. Dari data mahasiswa yang terdaftar, siapa sajakah (nama) mahasiswa yang tidak mengambil matakuliah ‘A01’?
4. Hapuslah data mahasiswa (dari tabel mhs) yang memiliki IPK terendah (kasus di DO).
5. Tampilkan mahasiswa yang memiliki IPK di bawah 3.5.
6. Untuk semua mahasiswa yang mengambil matakuliah ‘A03’, nilai matakuliah tersebut dinaikkan 1, karena ada kesalahan perhitungan nilai oleh dosennya 🙂

Wah cukup lumayan banyak juga ya pertanyaan-pertanyaan yang saya buat. OK untuk efisiensi, jawaban 6 pertanyaan di atas dibagi dalam 2 artikel. Untuk artikel pertama ini, hanya akan dibahas untuk pertanyaan 1 s/d 3 dulu.

OK akan kita jawab dulu pertanyaan No. 1.

Untuk pertanyaan ini, mungkin Anda mengira query atau statement SQL nya berbentuk seperti ini:

SELECT mhs.namaMhs, ambilmk.nilai FROM mhs, ambilmk WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A02' AND ambilmk.nilai = MAX(ambilmk.nilai);

Apabila query di atas dijalankan, maka akan terjadi error. Padahal secara logika sudah benar kan? Ternyata salahnya karena penggunaan aggregate function (dalam hal ini MAX) tidak boleh diletakkan dalam WHERE. Lantas di mana donk? Aggregate function hanya boleh diletakkan di bagian SELECT dan HAVING. Sehingga query yang benar adalah sebagai berikut:

SELECT mhs.namaMhs, ambilmk.nilai FROM mhs, ambilmk WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A02' AND ambilmk.nilai = (SELECT MAX(nilai) FROM ambilmk WHERE kodeMK = 'A02');

Perhatikan perintah di atas, terutama pada bagian SELECT MAX(nilai) FROM ambilmk WHERE kodeMK = ‘A02’. Bagian ini disebut dengan subquery. Perintah tersebut digunakan untuk mencari nilai tertinggi untuk matakuliah ‘A02’. Hasil dari subquery ini nantinya digunakan sebagai syarat untuk query yang berada di level atasnya (parent query).

Pertanyaan saya, haruskah ada perintah ambilmk.kodeMK = ‘A02’ dalam WHERE pada query di atas, toh kan sudah ada perintah kodeMK = ‘A02’ pada subquery? Bagaimana jika dihapus? Apa yang akan terjadi? Silakan Anda renungkan sendiri ya… 🙂

Selanjutnya kita bahas pertanyaan No. 2

Nah untuk pertanyaan ini, Anda mungkin juga mengira querynya berbentuk seperti ini

SELECT mhs.nim, mhs.namaMhs FROM mhs, ambilmk WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A03' AND ambilmk.nilai > AVG(ambilmk.nilai);

Apabila Anda jalankan, maka akan terjadi error. Penyebabnya sama dengan error yang terjadi pada soal No. 1, yaitu adanya aggregate function (dalam hal ini AVG()) dalam WHERE.

Statement SQL yang benar adalah:

SELECT mhs.nim, mhs.namaMhs FROM mhs, ambilmk WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A03' AND ambilmk.nilai > (SELECT AVG(nilai) FROM ambilmk WHERE kodeMK = 'A03');

Sudah mulai pahamkah Anda tentang subquery? Saya harapkan Anda sudah mulai paham, meskipun sedikit demi sedikit 🙂

OK… kita lanjutkan ke pertanyaan No. 3.

Secara logika, untuk menjawab pertanyaan ini adalah kita buat 2 buah query, yaitu query pertama untuk menampilkan semua mahasiswa yang terdaftar di database dan query kedua untuk menampilkan mahasiswa yang mengambil matakuliah ‘A01’. Selanjutnya hasil query pertama dibandingkan dengan hasil query kedua. Dan sebagai outputnya atau yang ditampilkan adalah mahasiswa yang ada di hasil query pertama namun tidak ada di hasil query kedua. Nah… bagaimana untuk mengimplementasikan logika ini di SQL? ini dia perintahnya

SELECT nim, namaMhs FROM mhs WHERE nim NOT IN (SELECT nim FROM ambilmk WHERE kodeMK = 'A01');

Maksud dari klausa WHERE nim NOT IN (SELECT nim FROM ambilmk WHERE kodeMK = ‘A01’) adalah bahwa syarat yang ditampilkan adalah nim yang ada di tabel mhs namun tidak terdapat (NOT IN) di hasil subquery SELECT nim FROM ambilmk WHERE kodeMK = ‘A01’ (nim yang mengambil ‘A01’). Sederhana bukan?

Bagikan artikel ini jika bermanfaat !

Academia.edu no longer supports Internet Explorer.

To browse Academia.edu and the wider internet faster and more securely, please take a few seconds to upgrade your browser.

• Artikel
• 20 menit untuk membaca

Berlaku untuk:

Subkueri adalah kueri yang ditumpuk di dalam SELECTpernyataan , , INSERTUPDATE, atau DELETE , atau di dalam subkueri lain.

Subkueri dapat digunakan di mana saja ekspresi diizinkan. Dalam contoh ini, subkueri digunakan sebagai ekspresi kolom bernama MaxUnitPrice dalam SELECT pernyataan.

Dasar-dasar subkueri

Subkueri juga disebut kueri dalam atau pemilihan dalam, sementara pernyataan yang berisi subkueri juga disebut kueri luar atau pemilihan luar.

Banyak pernyataan Transact-SQL yang menyertakan subkueri dapat diformulasikan sebagai gabungan. Pertanyaan lain hanya dapat diajukan dengan subkueri. Dalam Transact-SQL, biasanya tidak ada perbedaan performa antara pernyataan yang menyertakan subkueri dan versi setara semantik yang tidak. Untuk informasi arsitektur tentang cara SQL Server memproses kueri, lihat Pemrosesan pernyataan SQL. Namun, dalam beberapa kasus di mana keberadaan harus diperiksa, gabungan menghasilkan performa yang lebih baik. Jika tidak, kueri berlapis harus diproses untuk setiap hasil kueri luar untuk memastikan penghapusan duplikat. Dalam kasus seperti itu, pendekatan gabungan akan menghasilkan hasil yang lebih baik.

Contoh berikut menunjukkan subkueri SELECT dan gabungan SELECT yang mengembalikan tataan hasil dan rencana eksekusi yang sama:

Subkueri yang ditumpuk dalam pernyataan SELECT luar memiliki komponen berikut:

• Kueri reguler SELECT termasuk komponen daftar pilihan reguler.
• Klausa reguler FROM termasuk satu atau beberapa nama tabel atau tampilan.
• Klausa opsional WHERE .
• Klausa opsional GROUP BY .
• Klausa opsional HAVING .

Kueri SELECT dari subkueri selalu diapit dalam tanda kurung. Ini tidak dapat menyertakan COMPUTE klausul atau FOR BROWSE , dan hanya dapat menyertakan ORDER BY klausul ketika klausa TOP juga ditentukan.

Subkueri dapat disarangkan di dalam WHERE klausul atau HAVING dari pernyataan , , UPDATEINSERT, atau DELETE luar, atau di dalam subkueri SELECTlain. Hingga 32 tingkat bersarang dimungkinkan, meskipun batasnya bervariasi berdasarkan memori yang tersedia dan kompleksitas ekspresi lain dalam kueri. Kueri individual mungkin tidak mendukung bersarang hingga 32 tingkat. Subkueri dapat muncul di mana saja ekspresi dapat digunakan, jika mengembalikan satu nilai.

Jika tabel hanya muncul dalam subkueri dan bukan di kueri luar, maka kolom dari tabel tersebut tidak dapat disertakan dalam output (daftar pemilihan kueri luar).

Pernyataan yang menyertakan subkueri biasanya mengambil salah satu format berikut:

• WHERE expression [NOT] IN (subquery)
• WHERE expression comparison_operator [ANY | ALL] (subquery)
• WHERE [NOT] EXISTS (subquery)

Dalam beberapa pernyataan Transact-SQL, subkueri dapat dievaluasi seolah-olah itu adalah kueri independen. Secara konseptual, hasil subkueri diganti ke dalam kueri luar (meskipun ini belum tentu bagaimana SQL Server benar-benar memproses pernyataan Transact-SQL dengan subkueri).

Ada tiga jenis subkueri dasar. Mereka yang:

• Beroperasi pada daftar yang diperkenalkan dengan IN, atau yang dimodifikasi oleh ANY atau ALLoleh operator perbandingan.
• Diperkenalkan dengan operator perbandingan yang tidak dimodifikasi dan harus mengembalikan satu nilai.
• Apakah pengujian keberadaan diperkenalkan dengan EXISTS.

Aturan subkueri

Subkueri tunduk pada batasan berikut:

• Daftar pemilihan subkueri yang diperkenalkan dengan operator perbandingan hanya dapat menyertakan satu nama ekspresi atau kolom (kecuali yang EXISTS dan IN masing-masing beroperasi di SELECT * atau daftar).
• WHERE Jika klausa kueri luar menyertakan nama kolom, klausa kueri tersebut harus kompatibel dengan kolom dalam daftar pemilihan subkueri.
• Jenis data ntext, teks, dan gambar tidak dapat digunakan dalam daftar pemilihan subkueri.
• Karena mereka harus mengembalikan satu nilai, subkueri yang diperkenalkan oleh operator perbandingan yang tidak dimodifikasi (yang tidak diikuti oleh kata kunci ANY atau ALL) tidak dapat menyertakan GROUP BY dan HAVING klausa.
• Kata DISTINCT kunci tidak dapat digunakan dengan subkueri yang menyertakan 'GROUP BY.
• Klausa COMPUTE dan INTO tidak dapat ditentukan.
• ORDER BY hanya dapat ditentukan ketika TOP juga ditentukan.
• Tampilan yang dibuat dengan menggunakan subkueri tidak dapat diperbarui.
• Daftar pilih subkueri yang diperkenalkan dengan , menurut EXISTSkonvensi, memiliki tanda bintang (*) alih-alih satu nama kolom. Aturan untuk subkueri yang diperkenalkan sama dengan EXISTS aturan untuk daftar pemilihan standar, karena subkueri yang diperkenalkan dengan EXISTS membuat pengujian keberadaan dan mengembalikan TRUE atau FALSE, bukan data.

Memenuhi syarat nama kolom dalam subkueri

Dalam contoh berikut, kolom BusinessEntityID dalam WHERE klausul kueri luar secara implisit memenuhi syarat dengan nama tabel dalam klausa kueri FROM luar (Sales.Store). Referensi ke CustomerID dalam daftar pemilihan subkueri memenuhi syarat oleh klausa subkueri FROM , yaitu, oleh tabel Sales.Customer .

Aturan umum adalah bahwa nama kolom dalam pernyataan secara implisit memenuhi syarat oleh tabel yang dirujuk dalam FROM klausul pada tingkat yang sama. Jika kolom tidak ada dalam tabel yang direferensikan dalam FROM klausul subkueri, kolom tersebut secara implisit memenuhi syarat oleh tabel yang direferensikan dalam FROM klausa kueri luar.

Berikut tampilan kueri dengan asumsi implisit ini yang ditentukan:

Tidak pernah salah untuk menyatakan nama tabel secara eksplisit, dan selalu mungkin untuk mengambil alih asumsi implisit tentang nama tabel dengan kualifikasi eksplisit.

Penting

Jika kolom direferensikan dalam subkueri yang tidak ada dalam tabel yang direferensikan oleh klausa subkueri FROM , tetapi ada dalam tabel yang direferensikan oleh klausa kueri FROM luar, kueri dijalankan tanpa kesalahan. SQL Server secara implisit memenuhi syarat kolom dalam subkueri dengan nama tabel di kueri luar.

Beberapa tingkat bersarang

Subkueri itu sendiri dapat mencakup satu atau beberapa subkueri. Sejumlah subkueri dapat ditumpuk dalam pernyataan.

Kueri berikut menemukan nama karyawan yang juga merupakan tenaga penjualan.

Berikut adalah hasil yang ditetapkan.

Kueri terdalu mengembalikan ID orang penjualan. Kueri pada tingkat yang lebih tinggi berikutnya dievaluasi dengan ID orang penjualan ini dan mengembalikan nomor ID kontak karyawan. Terakhir, kueri luar menggunakan ID kontak untuk menemukan nama karyawan.

Anda juga bisa mengekspresikan kueri ini sebagai gabungan:

Banyak kueri dapat dievaluasi dengan menjalankan subkueri sekali dan mengganti nilai atau nilai yang dihasilkan ke dalam WHERE klausa kueri luar. Dalam kueri yang menyertakan subkueri berkorelasi (juga dikenal sebagai subkueri berulang), subkueri bergantung pada kueri luar untuk nilainya. Ini berarti bahwa subkueri dijalankan berulang kali, sekali untuk setiap baris yang mungkin dipilih oleh kueri luar. Kueri ini mengambil satu instans nama depan dan belakang setiap karyawan yang bonusnya dalam tabel SalesPerson adalah 5000 dan di mana nomor identifikasi karyawan cocok dalam tabel Karyawan dan Staf Penjualan .

Berikut adalah hasil yang ditetapkan.

Subkueri sebelumnya dalam pernyataan ini tidak dapat dievaluasi secara independen dari kueri luar. Ini membutuhkan nilai untuk Employee.BusinessEntityID, tetapi nilai ini berubah saat SQL Server memeriksa baris yang berbeda di Employee. Itulah cara kueri ini dievaluasi: SQL Server mempertimbangkan setiap baris tabel Karyawan untuk dimasukkan dalam hasil dengan mengganti nilai di setiap baris ke dalam kueri dalam. Misalnya, jika SQL Server pertama kali memeriksa baris untuk Syed Abbas, variabel Employee.BusinessEntityID mengambil nilai 285, yang SQL Server ganti ke dalam kueri dalam. Kedua sampel kueri ini mewakili penguraian sampel sebelumnya dengan subkueri yang berkorelasi.

Hasilnya adalah 0,00 (Syed Abbas tidak menerima bonus karena bukan orang penjualan), sehingga kueri luar mengevaluasi ke:

Karena ini salah, baris untuk Syed Abbas tidak disertakan dalam hasil kueri sampel sebelumnya dengan subkueri yang berkorelasi. Lakukan prosedur yang sama dengan baris untuk Pamela Ansman-Wolfe. Anda akan melihat bahwa baris ini disertakan dalam hasil, karena WHERE 5000 IN (5000) menyertakan hasil.

Subkueri yang berkorelasi juga dapat menyertakan fungsi bernilai tabel dalam FROM klausul dengan mereferensikan kolom dari tabel di kueri luar sebagai argumen fungsi bernilai tabel. Dalam hal ini, untuk setiap baris kueri luar, fungsi bernilai tabel dievaluasi sesuai dengan subkueri.

Jenis subkueri

Subkueri dapat ditentukan di banyak tempat:

• Dengan alias. Untuk informasi selengkapnya, lihat Subkueri dengan Alias.
• Dengan IN atau NOT IN. Untuk informasi selengkapnya, lihat Subkueri dengan IN dan Subkueri dengan NOT IN.
• Dalam UPDATEpernyataan , DELETE, dan INSERT . Untuk informasi selengkapnya, lihat Subkueri dalam Pernyataan UPDATE, DELETE, dan INSERT.
• Dengan operator perbandingan. Untuk informasi selengkapnya, lihat Subkueri dengan Operator Perbandingan.
• Dengan ANY, SOME, atau ALL. Untuk informasi selengkapnya, lihat Operator Perbandingan yang Dimodifikasi oleh ANY, SOME, atau ALL.
• Dengan IS [NOT] DISTINCT FROM. Untuk informasi selengkapnya, lihat IS [NOT] DISTINCT FROM (Transact-SQL).
• Dengan EXISTS atau NOT EXISTS. Untuk informasi selengkapnya, lihat Subkueri dengan EXISTS dan Subkueri dengan NOT EXISTS.
• Menggantikan ekspresi. Untuk informasi selengkapnya, lihat Subkueri yang digunakan sebagai pengganti Ekspresi.

Subkueri dengan alias tabel

Banyak pernyataan di mana kueri subkueri dan kueri luar merujuk ke tabel yang sama dapat dinyatakan sebagai gabungan mandiri (menggabungkan tabel ke dirinya sendiri). Misalnya, Anda dapat menemukan alamat karyawan dari status tertentu menggunakan subkueri:

Berikut adalah hasil yang ditetapkan.

Atau Anda dapat menggunakan gabungan mandiri:

Alias tabel e1 dan e2 diperlukan karena tabel digabungkan dengan sendirinya muncul dalam dua peran yang berbeda. Alias juga dapat digunakan dalam kueri berlapis yang merujuk ke tabel yang sama dalam kueri dalam dan luar.

Alias tabel eksplisit memperjelas bahwa referensi ke Person.Address dalam subkueri tidak berarti hal yang sama seperti referensi dalam kueri luar.

Subkueri dengan IN

Hasil dari subkueri yang diperkenalkan dengan IN (atau dengan NOT IN) adalah daftar nilai nol atau lebih. Setelah subkueri mengembalikan hasil, kueri luar menggunakannya. Kueri berikut menemukan nama semua produk roda yang dilakukan Adventure Works Cycles.

Berikut adalah hasil yang ditetapkan.

Pernyataan ini dievaluasi dalam dua langkah. Pertama, kueri dalam mengembalikan nomor identifikasi subkataan yang cocok dengan nama 'Wheel' (17). Kedua, nilai ini diganti ke dalam kueri luar, yang menemukan nama produk yang sesuai dengan nomor identifikasi subkategori di Production.Product.

Salah satu perbedaan dalam menggunakan gabungan daripada subkueri untuk ini dan masalah serupa adalah bahwa gabungan memungkinkan Anda menampilkan kolom dari lebih dari satu tabel dalam hasilnya. Misalnya, jika Anda ingin menyertakan nama subkataan produk dalam hasilnya, Anda harus menggunakan versi gabungan.

Berikut adalah hasil yang ditetapkan.

Kueri berikut menemukan nama semua vendor yang peringkat kreditnya bagus, dari siapa Adventure Works Cycles memesan setidaknya 20 item, dan yang waktu tunggu rata-rata pengirimannya kurang dari 16 hari.

Berikut adalah hasil yang ditetapkan.

Kueri dalam dievaluasi, menghasilkan nomor ID vendor yang memenuhi kualifikasi subkueri. Kueri luar kemudian dievaluasi. Perhatikan bahwa Anda dapat menyertakan lebih dari satu kondisi dalam WHERE klausa kueri dalam dan luar.

Menggunakan gabungan, kueri yang sama dinyatakan seperti ini:

Gabungan selalu dapat diekspresikan sebagai subkueri. Subkueri sering dapat, tetapi tidak selalu, dinyatakan sebagai gabungan. Ini karena gabungan bersifat simetris: Anda dapat menggabungkan tabel A hingga B dalam urutan dan mendapatkan jawaban yang sama. Hal yang sama tidak benar jika subkueri terlibat.

Subkueri dengan NOT IN

Subkueri yang diperkenalkan dengan kata kunci NOT IN juga mengembalikan daftar nilai nol atau lebih. Kueri berikut menemukan nama produk yang belum selesai sepeda.

Pernyataan ini tidak dapat dikonversi menjadi gabungan. Gabungan yang tidak sama dengan analog memiliki arti yang berbeda: Ini menemukan nama produk yang ada di beberapa subkataan yang bukan sepeda jadi.

Subkueri dalam UPDATEpernyataan , DELETE, dan INSERT

Subkueri dapat disarangkan dalam UPDATEDELETEpernyataan , , INSERT dan SELECT manipulasi data (DML).

Contoh berikut menggandakan nilai di kolom ListPrice dalam tabel Production.Product . Subkueri dalam WHERE klausul mereferensikan tabel Purchasing.ProductVendor untuk membatasi baris yang diperbarui dalam tabel Produk hanya untuk yang disediakan oleh BusinessEntity 1540.

Berikut adalah pernyataan yang setara UPDATE menggunakan gabungan:

Untuk kejelasan jika tabel yang sama itu sendiri direferensikan dalam subkueri lain, gunakan alias tabel target:

Subkueri dengan operator perbandingan

Subkueri dapat diperkenalkan dengan salah satu operator perbandingan (=, , < >, > =>, <, ! >, ! <atau < =).

Subkueri yang diperkenalkan dengan operator perbandingan yang tidak dimodifikasi (operator perbandingan yang tidak diikuti oleh ANY atau ALL) harus mengembalikan satu nilai daripada daftar nilai, seperti subkueri yang diperkenalkan dengan IN. Jika subkueri tersebut mengembalikan lebih dari satu nilai, SQL Server menampilkan pesan kesalahan.

Untuk menggunakan subkueri yang diperkenalkan dengan operator perbandingan yang tidak dimodifikasi, Anda harus cukup terbiasa dengan data Anda dan dengan sifat masalah untuk mengetahui bahwa subkueri akan mengembalikan tepat satu nilai.

Misalnya, jika Anda menganggap setiap orang penjualan hanya mencakup satu wilayah penjualan, dan Anda ingin menemukan pelanggan yang terletak di wilayah yang dicakup oleh Linda Mitchell, Anda dapat menulis pernyataan dengan subkueri yang diperkenalkan dengan operator perbandingan sederhana = .

Namun, Linda Mitchell jika mencakup lebih dari satu wilayah penjualan, maka pesan kesalahan akan menghasilkan. Alih-alih operator perbandingan = , IN rumusan dapat digunakan (=ANY juga berfungsi).

Subkueri yang diperkenalkan dengan operator perbandingan yang tidak dimodifikasi sering menyertakan fungsi agregat, karena ini mengembalikan satu nilai. Misalnya, pernyataan berikut menemukan nama semua produk yang harga daftarnya lebih besar dari harga daftar rata-rata.

Karena subkueri yang diperkenalkan dengan operator perbandingan yang tidak dimodifikasi harus mengembalikan satu nilai, mereka tidak dapat menyertakan GROUP BY klausul atau HAVING kecuali Anda mengetahui GROUP BY klausa atau HAVING itu sendiri mengembalikan satu nilai. Misalnya, kueri berikut menemukan produk dengan harga lebih tinggi dari produk dengan harga terendah yang ada di ProductSubcategoryID 14.

Operator perbandingan yang dimodifikasi oleh ANY, SOME, atau ALL

Operator perbandingan yang memperkenalkan subkueri dapat dimodifikasi oleh kata kunci ALL atau ANY. SOME adalah standar ISO yang setara untuk ANY. Untuk informasi selengkapnya tentang operator perbandingan ini, lihat BEBERAPA | SEMUA.

Subkueri yang diperkenalkan dengan operator perbandingan yang dimodifikasi mengembalikan daftar nilai nol atau lebih dan dapat menyertakan GROUP BY klausa atau HAVING . Subkueri ini dapat dipulihkan dengan EXISTS.

Menggunakan operator perbandingan > sebagai contoh, > ALL berarti lebih besar dari setiap nilai. Dengan kata lain, itu berarti lebih besar dari nilai maksimum. Misalnya, > ALL (1, 2, 3) berarti lebih besar dari 3. > ANY berarti lebih besar dari setidaknya satu nilai, yaitu, lebih besar dari minimum. Jadi > ANY (1, 2, 3) berarti lebih besar dari 1.

Untuk baris dalam subkueri dengan > ALL untuk memenuhi kondisi yang ditentukan dalam kueri luar, nilai dalam kolom yang memperkenalkan subkueri harus lebih besar dari setiap nilai dalam daftar nilai yang dikembalikan oleh subkueri.

Demikian pula, > ANY berarti bahwa untuk baris untuk memenuhi kondisi yang ditentukan dalam kueri luar, nilai dalam kolom yang memperkenalkan subkueri harus lebih besar dari setidaknya salah satu nilai dalam daftar nilai yang dikembalikan oleh subkueri.

Kueri berikut ini menyediakan contoh subkueri yang diperkenalkan dengan operator perbandingan yang dimodifikasi oleh ANY. Ini menemukan produk yang daftar harganya lebih besar dari atau sama dengan harga daftar maksimum subkategorang produk apa pun.

Untuk setiap subkategrasi Produk, kueri dalam menemukan harga daftar maksimum. Kueri luar melihat semua nilai ini dan menentukan harga daftar produk individu mana yang lebih besar dari atau sama dengan harga daftar maksimum subkategorang produk apa pun. Jika ANY diubah menjadi ALL, kueri hanya akan mengembalikan produk yang harga daftarnya lebih besar dari atau sama dengan semua harga daftar yang dikembalikan dalam kueri dalam.

Jika subkueri tidak mengembalikan nilai apa pun, seluruh kueri gagal mengembalikan nilai apa pun.

Operator = ANY setara dengan IN. Misalnya, untuk menemukan nama semua produk roda yang dilakukan Adventure Works Cycles, Anda dapat menggunakan atau IN= ANY.

Berikut adalah hasil yang ditetapkan untuk salah satu kueri:

Operator <> ANY , bagaimanapun, berbeda dari NOT IN:

• <> ANY berarti tidak = a, atau tidak = b, atau tidak = c
• NOT IN berarti tidak = a, dan bukan = b, dan bukan = c
• <> ALL berarti sama seperti NOT IN

Misalnya, kueri berikut menemukan pelanggan yang terletak di wilayah yang tidak dicakup oleh orang penjualan mana pun.

Hasilnya mencakup semua pelanggan, kecuali mereka yang wilayah penjualannya NULL, karena setiap wilayah yang ditetapkan untuk pelanggan dicakup oleh orang penjualan. Kueri dalam menemukan semua wilayah penjualan yang dicakup oleh orang penjualan, lalu, untuk setiap wilayah, kueri luar menemukan pelanggan yang tidak berada dalam satu wilayah.

Untuk alasan yang sama, saat Anda menggunakan NOT IN dalam kueri ini, hasilnya tidak menyertakan pelanggan.

Anda bisa mendapatkan hasil yang sama dengan <> ALL operator, yang setara NOT INdengan .

Subkueri dengan EXISTS

Ketika subkueri diperkenalkan dengan kata kunci EXISTS, subkueri berfungsi sebagai pengujian keberadaan. Klausa WHERE kueri luar menguji apakah baris yang dikembalikan oleh subkueri ada. Subkueri sebenarnya tidak menghasilkan data apa pun; mengembalikan nilai TRUE atau FALSE.

Subkueri yang diperkenalkan dengan EXISTS memiliki sintaks berikut:

WHERE [NOT] EXISTS (subquery)

Kueri berikut menemukan nama semua produk yang ada di subkataan Roda:

Berikut adalah hasil yang ditetapkan.

Untuk memahami hasil kueri ini, pertimbangkan nama setiap produk secara bergantian. Apakah nilai ini menyebabkan subkueri mengembalikan setidaknya satu baris? Dengan kata lain, apakah kueri menyebabkan pengujian keberadaan dievaluasi ke TRUE?

Perhatikan bahwa subkueri yang diperkenalkan dengan EXISTS sedikit berbeda dari subkueri lain dengan cara berikut:

• Kata kunci EXISTS tidak didahului oleh nama kolom, konstanta, atau ekspresi lainnya.
• Daftar pemilihan subkueri yang diperkenalkan oleh EXISTS hampir selalu terdiri dari tanda bintang (*). Tidak ada alasan untuk mencantumkan nama kolom karena Anda hanya menguji apakah baris yang memenuhi kondisi yang ditentukan dalam subkueri ada.

Kata EXISTS kunci penting karena sering tidak ada rumusan alternatif tanpa subkueri. Meskipun beberapa kueri yang dibuat dengan EXISTS tidak dapat diekspresikan dengan cara lain, banyak kueri dapat menggunakan IN atau operator perbandingan yang dimodifikasi oleh ANY atau ALL untuk mencapai hasil serupa.

Misalnya, kueri sebelumnya dapat diekspresikan dengan menggunakan IN:

Subkueri dengan NOT EXISTS

NOT EXISTS berfungsi seperti EXISTS, kecuali WHERE klausul di mana ia digunakan terpenuhi jika tidak ada baris yang dikembalikan oleh subkueri.

Misalnya, untuk menemukan nama produk yang tidak ada di subkataan roda:

Subkueri yang digunakan sebagai pengganti ekspresi

Dalam Transact-SQL, subkueri dapat diganti di mana saja ekspresi dapat digunakan dalam SELECTpernyataan , , UPDATEINSERT, dan DELETE , kecuali dalam ORDER BY daftar.

Contoh berikut menggambarkan bagaimana Anda dapat menggunakan peningkatan ini. Kueri ini menemukan harga semua produk sepeda gunung, harga rata-ratanya, dan perbedaan antara harga setiap sepeda gunung dan harga rata-rata.

Lihat juga

Sintaks

Konsep performa kueri