EXISTS (Transact-SQL)
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)Titik akhir analitik SQL di Microsoft FabricWarehouse di Microsoft Fabric
Menentukan subkueri untuk menguji keberadaan baris.
Sintaksis
EXISTS ( subquery )
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
kueri bertumpuk
Adalah pernyataan SELECT terbatas. Kata kunci INTO tidak diperbolehkan. Untuk informasi selengkapnya, lihat informasi tentang subkueri di SELECT (Transact-SQL).
Jenis Hasil
Boolean
Nilai Hasil
Mengembalikan TRUE jika subkueri berisi baris apa pun.
Contoh
J. Menggunakan NULL dalam subkueri untuk masih mengembalikan tataan hasil
Contoh berikut mengembalikan tataan hasil dengan NULL yang ditentukan dalam subkueri dan masih mengevaluasi ke TRUE dengan menggunakan EXISTS.
-- Uses AdventureWorks
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE EXISTS (SELECT NULL)
ORDER BY Name ASC ;
B. Membandingkan kueri dengan menggunakan EXISTS dan IN
Contoh berikut membandingkan dua kueri yang setara secara semantik. Kueri pertama menggunakan EXISTS dan kueri kedua menggunakan IN.
-- Uses AdventureWorks
SELECT a.FirstName, a.LastName
FROM Person.Person AS a
WHERE EXISTS
(SELECT *
FROM HumanResources.Employee AS b
WHERE a.BusinessEntityID = b.BusinessEntityID
AND a.LastName = 'Johnson') ;
GO
Kueri berikut menggunakan IN.
-- Uses AdventureWorks
SELECT a.FirstName, a.LastName
FROM Person.Person AS a
WHERE a.LastName IN
(SELECT a.LastName
FROM HumanResources.Employee AS b
WHERE a.BusinessEntityID = b.BusinessEntityID
AND a.LastName = 'Johnson') ;
GO
Berikut adalah hasil yang ditetapkan untuk salah satu kueri.
FirstName LastName
-------------------------------------------------- ----------
Barry Johnson
David Johnson
Willis Johnson
(3 row(s) affected)
C. Membandingkan kueri dengan menggunakan EXISTS dan = ANY
Contoh berikut menunjukkan dua kueri untuk menemukan penyimpanan yang namanya sama dengan vendor. Kueri pertama menggunakan EXISTS dan yang kedua menggunakan = ANY.
-- Uses AdventureWorks
SELECT DISTINCT s.Name
FROM Sales.Store AS s
WHERE EXISTS
(SELECT *
FROM Purchasing.Vendor AS v
WHERE s.Name = v.Name) ;
GO
Kueri berikut menggunakan = ANY.
-- Uses AdventureWorks
SELECT DISTINCT s.Name
FROM Sales.Store AS s
WHERE s.Name = ANY
(SELECT v.Name
FROM Purchasing.Vendor AS v ) ;
GO
D. Membandingkan kueri dengan menggunakan EXISTS dan IN
Contoh berikut menunjukkan kueri untuk menemukan karyawan departemen yang dimulai dengan P.
-- Uses AdventureWorks
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
WHERE EXISTS
(SELECT *
FROM HumanResources.Department AS d
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON d.DepartmentID = edh.DepartmentID
WHERE e.BusinessEntityID = edh.BusinessEntityID
AND d.Name LIKE 'P%') ;
GO
Kueri berikut menggunakan IN.
-- Uses AdventureWorks
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE edh.DepartmentID IN
(SELECT DepartmentID
FROM HumanResources.Department
WHERE Name LIKE 'P%') ;
GO
E. Menggunakan NOT EXISTS
NOT EXISTS bekerja kebalikan dari EXISTS. Klausa WHERE di NOT EXISTS terpenuhi jika tidak ada baris yang dikembalikan oleh subkueri. Contoh berikut menemukan karyawan yang tidak berada di departemen yang memiliki nama yang dimulai dengan P.
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT EXISTS
(SELECT *
FROM HumanResources.Department AS d
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON d.DepartmentID = edh.DepartmentID
WHERE e.BusinessEntityID = edh.BusinessEntityID
AND d.Name LIKE 'P%')
ORDER BY LastName, FirstName
GO
Berikut adalah hasil yang ditetapkan.
FirstName LastName Title
------------------------------ ------------------------------ ------------
Syed Abbas Pacific Sales Manager
Hazem Abolrous Quality Assurance Manager
Humberto Acevedo Application Specialist
Pilar Ackerman Shipping & Receiving Superviso
François Ajenstat Database Administrator
Amy Alberts European Sales Manager
Sean Alexander Quality Assurance Technician
Pamela Ansman-Wolfe Sales Representative
Zainal Arifin Document Control Manager
David Barber Assistant to CFO
Paula Barreto de Mattos Human Resources Manager
Shai Bassli Facilities Manager
Wanida Benshoof Marketing Assistant
Karen Berg Application Specialist
Karen Berge Document Control Assistant
Andreas Berglund Quality Assurance Technician
Matthias Berndt Shipping & Receiving Clerk
Jo Berry Janitor
Jimmy Bischoff Stocker
Michael Blythe Sales Representative
David Bradley Marketing Manager
Kevin Brown Marketing Assistant
David Campbell Sales Representative
Jason Carlson Information Services Manager
Fernando Caro Sales Representative
Sean Chai Document Control Assistant
Sootha Charncherngkha Quality Assurance Technician
Hao Chen HR Administrative Assistant
Kevin Chrisulis Network Administrator
Pat Coleman Janitor
Stephanie Conroy Network Manager
Debra Core Application Specialist
Ovidiu Crãcium Sr. Tool Designer
Grant Culbertson HR Administrative Assistant
Mary Dempsey Marketing Assistant
Thierry D'Hers Tool Designer
Terri Duffy VP Engineering
Susan Eaton Stocker
Terry Eminhizer Marketing Specialist
Gail Erickson Design Engineer
Janice Galvin Tool Designer
Mary Gibson Marketing Specialist
Jossef Goldberg Design Engineer
Sariya Harnpadoungsataya Marketing Specialist
Mark Harrington Quality Assurance Technician
Magnus Hedlund Facilities Assistant
Shu Ito Sales Representative
Stephen Jiang North American Sales Manager
Willis Johnson Recruiter
Brannon Jones Finance Manager
Tengiz Kharatishvili Control Specialist
Christian Kleinerman Maintenance Supervisor
Vamsi Kuppa Shipping & Receiving Clerk
David Liu Accounts Manager
Vidur Luthra Recruiter
Stuart Macrae Janitor
Diane Margheim Research & Development Enginee
Mindy Martin Benefits Specialist
Gigi Matthew Research & Development Enginee
Tete Mensa-Annan Sales Representative
Ramesh Meyyappan Application Specialist
Dylan Miller Research & Development Manager
Linda Mitchell Sales Representative
Barbara Moreland Accountant
Laura Norman Chief Financial Officer
Chris Norred Control Specialist
Jae Pak Sales Representative
Wanda Parks Janitor
Deborah Poe Accounts Receivable Specialist
Kim Ralls Stocker
Tsvi Reiter Sales Representative
Sharon Salavaria Design Engineer
Ken Sanchez Chief Executive Officer
José Saraiva Sales Representative
Mike Seamans Accountant
Ashvini Sharma Network Administrator
Janet Sheperdigian Accounts Payable Specialist
Candy Spoon Accounts Receivable Specialist
Michael Sullivan Sr. Design Engineer
Dragan Tomic Accounts Payable Specialist
Lynn Tsoflias Sales Representative
Rachel Valdez Sales Representative
Garrett Vargar Sales Representative
Ranjit Varkey Chudukatil Sales Representative
Bryan Walton Accounts Receivable Specialist
Jian Shuo Wang Engineering Manager
Brian Welcker VP Sales
Jill Williams Marketing Specialist
Dan Wilson Database Administrator
John Wood Marketing Specialist
Peng Wu Quality Assurance Supervisor
(91 row(s) affected)
Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)
F. Menggunakan EXISTS
Contoh berikut mengidentifikasi apakah ada baris dalam ProspectiveBuyer tabel yang bisa cocok dengan baris dalam DimCustomer tabel. Kueri akan mengembalikan baris hanya ketika nilai LastName dan BirthDate dalam dua tabel cocok.
-- Uses AdventureWorks
SELECT a.LastName, a.BirthDate
FROM DimCustomer AS a
WHERE EXISTS
(SELECT *
FROM dbo.ProspectiveBuyer AS b
WHERE (a.LastName = b.LastName) AND (a.BirthDate = b.BirthDate)) ;
G. Menggunakan NOT EXISTS
NOT EXISTS berfungsi sebagai kebalikan dari EXISTS. Klausa WHERE di NOT EXISTS terpenuhi jika tidak ada baris yang dikembalikan oleh subkueri. Contoh berikut menemukan baris dalam DimCustomer tabel di mana LastName dan BirthDate tidak cocok dengan entri apa pun dalam ProspectiveBuyers tabel.
-- Uses AdventureWorks
SELECT a.LastName, a.BirthDate
FROM DimCustomer AS a
WHERE NOT EXISTS
(SELECT *
FROM dbo.ProspectiveBuyer AS b
WHERE (a.LastName = b.LastName) AND (a.BirthDate = b.BirthDate)) ;
Lihat Juga
Ekspresi (Transact-SQL)
Fungsi Bawaan (Transact-SQL)
WHERE (Transact-SQL)
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk