Bagikan melalui

Subkueri (SQL Server)

  • Artikel

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

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

USE AdventureWorks2022;
GO
SELECT Ord.SalesOrderID, Ord.OrderDate,
    (SELECT MAX(OrdDet.UnitPrice)
     FROM Sales.SalesOrderDetail AS OrdDet
     WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM Sales.SalesOrderHeader AS Ord;
GO

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 yang setara secara 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:

USE AdventureWorks2022;
GO

/* SELECT statement built using a subquery. */
SELECT [Name]
FROM Production.Product
WHERE ListPrice =
    (SELECT ListPrice
     FROM Production.Product
     WHERE [Name] = 'Chainring Bolts' );
GO

/* SELECT statement built using a join that returns
   the same result set. */
SELECT Prd1.[Name]
FROM Production.Product AS Prd1
     JOIN Production.Product AS Prd2
       ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2.[Name] = 'Chainring Bolts';
GO

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 klausul TOP juga ditentukan.

Subkueri dapat disarangkan di dalam WHERE atau HAVING klausa luar , , INSERT, UPDATEatau DELETE pernyataan, 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 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 operator perbandingan yang dimodifikasi oleh ANY atau ALL.
  • Diperkenalkan dengan operator perbandingan yang tidak dimodifikasi dan harus mengembalikan satu nilai.
  • Apakah tes 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 pada 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 pemilihan subkueri yang diperkenalkan dengan EXISTS, menurut konvensi, 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, BusinessEntityID kolom 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, menurut Sales.Customer tabel.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Sales.Store
WHERE BusinessEntityID NOT IN
    (SELECT CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5);
GO

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 klausul kueri luar.

Berikut tampilan kueri dengan asumsi implisit ini yang ditentukan:

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Sales.Store
WHERE Sales.Store.BusinessEntityID NOT IN
    (SELECT Sales.Customer.CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5);
GO

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 dapat menyertakan satu atau beberapa subkueri. Sejumlah subkueri dapat ditumpuk dalam pernyataan.

Kueri berikut menemukan nama karyawan yang juga merupakan tenaga penjualan.

USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person
WHERE BusinessEntityID IN
    (SELECT BusinessEntityID
     FROM HumanResources.Employee
     WHERE BusinessEntityID IN
        (SELECT BusinessEntityID
         FROM Sales.SalesPerson)
    );
GO

Berikut set hasilnya.

LastName                                           FirstName
-------------------------------------------------- -----------------------
Jiang                                              Stephen
Abbas                                              Syed
Alberts                                            Amy
Ansman-Wolfe                                       Pamela
Campbell                                           David
Carson                                             Jillian
Ito                                                Shu
Mitchell                                           Linda
Reiter                                             Tsvi
Saraiva                                            Jos
Vargas                                             Garrett
Varkey Chudukatil                                  Ranjit
Valdez                                             Rachel
Tsoflias                                           Lynn
Pak                                                Jae
Blythe                                             Michael
Mensa-Annan                                        Tete

(17 row(s) affected)

Kueri paling dalam 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 dapat mengekspresikan kueri ini sebagai gabungan:

USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person c
INNER JOIN HumanResources.Employee e
ON c.BusinessEntityID = e.BusinessEntityID
JOIN Sales.SalesPerson s
ON e.BusinessEntityID = s.BusinessEntityID;
GO

Subkueri berkorelasi

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 yang 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 SalesPerson tabel adalah 5000 dan di mana nomor identifikasi karyawan cocok dalam Employee tabel dan SalesPerson .

USE AdventureWorks2022;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO

Berikut set hasilnya.

LastName FirstName BusinessEntityID
-------------------------- ---------- ------------
Ansman-Wolfe Pamela 280
Saraiva José 282

(2 row(s) affected)

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 tepatnya bagaimana kueri ini dievaluasi: SQL Server mempertimbangkan setiap baris Employee tabel 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 diganti SQL Server ke dalam kueri dalam. Kedua sampel kueri ini mewakili dekomposisi sampel sebelumnya dengan subkueri yang berkorelasi.

USE AdventureWorks2022;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE BusinessEntityID = 285;
GO

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

USE AdventureWorks2022;
GO
SELECT LastName, FirstName
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000 IN (0.00);
GO

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 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 dalam 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:

Subkueri dengan alias tabel

Banyak pernyataan di mana 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:

USE AdventureWorks2022;
GO
SELECT StateProvinceID, AddressID
FROM Person.Address
WHERE AddressID IN
    (SELECT AddressID
     FROM Person.Address
     WHERE StateProvinceID = 39);
GO

Berikut set hasilnya.

StateProvinceID AddressID
----------- -----------
39 942
39 955
39 972
39 22660

(4 row(s) affected)

Atau Anda dapat menggunakan gabungan mandiri:

USE AdventureWorks2022;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
INNER JOIN Person.Address AS e2
ON e1.AddressID = e2.AddressID
AND e2.StateProvinceID = 39;
GO

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.

USE AdventureWorks2022;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
WHERE e1.AddressID IN
    (SELECT e2.AddressID
     FROM Person.Address AS e2
     WHERE e2.StateProvinceID = 39);
GO

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

Subkueri dengan IN

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

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE [Name] = 'Wheels');
GO

Berikut set hasilnya.

Name
----------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

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 masuk dengan nomor identifikasi subkategori di Production.Product.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN ('17');
GO

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

USE AdventureWorks2022;
GO
SELECT p.[Name], s.[Name]
FROM Production.Product p
INNER JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
AND s.[Name] = 'Wheels';
GO

Berikut set hasilnya.

Name
LL Mountain Front Wheel Wheels
ML Mountain Front Wheel Wheels
HL Mountain Front Wheel Wheels
LL Road Front Wheel Wheels
ML Road Front Wheel Wheels
HL Road Front Wheel Wheels
Touring Front Wheel Wheels
LL Mountain Rear Wheel Wheels
ML Mountain Rear Wheel Wheels
HL Mountain Rear Wheel Wheels
LL Road Rear Wheel Wheels
ML Road Rear Wheel Wheels
HL Road Rear Wheel Wheels
Touring Rear Wheel Wheels

(14 row(s) affected)

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

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Purchasing.Vendor
WHERE CreditRating = 1
AND BusinessEntityID IN
    (SELECT BusinessEntityID
     FROM Purchasing.ProductVendor
     WHERE MinOrderQty >= 20
     AND AverageLeadTime < 16);
GO

Berikut set hasilnya.

Name
--------------------------------------------------
Compete Enterprises, Inc
International Trek Center
First National Sport Co.
Comfort Road Bicycles
Circuit Cycles
First Rate Bicycles
Jeff's Sporting Goods
Competition Bike Training Systems
Electronic Bike Repair & Supplies
Crowley Sport
Expert Bike Co
Team Athletic Co.
Compete, Inc.

(13 row(s) affected)

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

Menggunakan gabungan, kueri yang sama dinyatakan seperti ini:

USE AdventureWorks2022;
GO
SELECT DISTINCT [Name]
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor p
ON v.BusinessEntityID = p.BusinessEntityID
WHERE CreditRating = 1
  AND MinOrderQty >= 20
  AND AverageLeadTime < 16;
GO

Gabungan selalu dapat dinyatakan sebagai subkueri. Subkueri sering dapat, tetapi tidak selalu, dinyatakan sebagai gabungan. Ini karena gabungan bersifat simetris: Anda dapat menggabungkan tabel A dalam B urutan dan mendapatkan jawaban yang sama. Hal yang sama tidak berlaku 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.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID NOT IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE [Name] = 'Mountain Bikes'
        OR [Name] = 'Road Bikes'
        OR [Name] = 'Touring Bikes');
GO

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 pernyataan UPDATE, DELETE, dan INSERT

Subkueri dapat disarangkan dalam UPDATEpernyataan , , DELETEINSERT, dan SELECT manipulasi data (DML).

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

USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
    (SELECT ProductID
     FROM Purchasing.ProductVendor
     WHERE BusinessEntityID = 1540);
GO

Berikut adalah pernyataan yang setara UPDATE menggunakan gabungan:

USE AdventureWorks2022;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
    ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;
GO

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

USE AdventureWorks2022;
GO
UPDATE p
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
    ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;
GO

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 seperti itu 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 mengasumsikan 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 = .

USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID =
    (SELECT TerritoryID
     FROM Sales.SalesPerson
     WHERE BusinessEntityID = 276);
GO

Namun, Linda Mitchell jika mencakup lebih dari satu wilayah penjualan, maka pesan kesalahan akan dihasilkan. 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.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
    (SELECT AVG (ListPrice)
     FROM Production.Product);
GO

Karena subkueri yang diperkenalkan dengan operator perbandingan yang tidak dimodifikasi harus mengembalikan satu nilai, mereka tidak dapat menyertakan GROUP BY atau HAVING klausul 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.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >
    (SELECT MIN (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID
     HAVING ProductSubcategoryID = 14);
GO

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 | ADA.

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 harga daftarnya lebih besar dari atau sama dengan harga daftar maksimum subkategorer produk apa pun.

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ListPrice >= ANY
    (SELECT MAX (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID);
GO

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 mengembalikan produk yang harga daftarnya lebih besar dari atau sama dengan semua harga daftar yang dikembalikan dalam kueri.

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.

--Using = ANY
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID = ANY
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels');
GO

--Using IN
USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels');
GO

Berikut adalah hasil yang ditetapkan untuk salah satu kueri:

Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

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 apa pun.

USE AdventureWorks2022;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
    (SELECT TerritoryID
     FROM Sales.SalesPerson);
GO

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, dan kemudian, untuk setiap wilayah, kueri luar menemukan pelanggan yang tidak berada di dalamnya.

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

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

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 tidak benar-benar menghasilkan data apa pun; ini 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:

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE EXISTS
    (SELECT *
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID =
            Production.Product.ProductSubcategoryID
        AND [Name] = 'Wheels');
GO

Berikut set hasilnya.

Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel

(14 row(s) affected)

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 ?TRUE

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 tidak dapat diekspresikan dengan EXISTS cara lain, banyak kueri yang dapat digunakan IN atau operator perbandingan yang dimodifikasi oleh ANY atau ALL untuk mencapai hasil yang sama.

Misalnya, kueri sebelumnya dapat diekspresikan dengan menggunakan IN:

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE [Name] = 'Wheels');
GO

Subkueri dengan NOT EXISTS

NOT EXISTS berfungsi seperti EXISTS, kecuali WHERE klausa terpenuhi jika tidak ada baris yang dikembalikan oleh subkueri.

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

USE AdventureWorks2022;
GO
SELECT [Name]
FROM Production.Product
WHERE NOT EXISTS
    (SELECT *
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID =
            Production.Product.ProductSubcategoryID
        AND [Name] = 'Wheels');
GO

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 mengilustrasikan 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.

USE AdventureWorks2022;
GO
SELECT [Name], ListPrice,
(SELECT AVG(ListPrice) FROM Production.Product) AS Average,
    ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)
    AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1;
GO

Konten terkait