SQL - EXISTS 연산자

EXISTS

EXISTS 연산자는 하위 쿼리에 레코드가 있는지 테스트하는 데 사용된다.

하위 쿼리가 하나 이상의 레코드를 반환하는 경우 EXISTS 연산자는 TRUE를 반환한다.

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18
2	Chang	1	1	24 – 12 oz bottles	19
3	Aniseed Syrup	1	2	12 – 550 ml bottles	10
4	Chef Anton’s Cajun Seasoning	2	2	48 – 6 oz jars	22
5	Chef Anton’s Gumbo Mix	2	2	36 boxes	21.35

SupplierID	SupplierName	ContactName	Address	City	PostalCode	Country
1	Exotic Liquid	Charlotte Cooper	49 Gilbert St.	London	EC1 4SD	UK
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA
3	Grandma Kelly’s Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA
4	Tokyo Traders	Yoshi Nagase	9-8 Sekimai Musashino-shi	Tokyo	100	Japan

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

기본 예시

SupplierName
Exotic Liquid
New Orleans Cajun Delights
Tokyo Traders
Mayumi's
Pavlova, Ltd.
Specialty Biscuits, Ltd.
PB Knäckebröd AB
Refrescos Americanas LTDA
Heli Süßwaren GmbH & Co. KG
Plutzer Lebensmittelgroßmärkte AG
Formaggi Fortini s.r.l.
Norske Meierier
Bigfoot Breweries
Svensk Sjöföda AB
Aux joyeux ecclésiastiques
New England Seafood Cannery
Leka Trading
Lyngbysild
Zaanse Snoepfabriek
Karkki Oy
G'day, Mate
Ma Maison
Pasta Buttini s.r.l.
Escargots Nouveaux

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);

기본 예시

SupplierName
New Orleans Cajun Delights

W3C School - SQL EXISTS Operator

W3C School - SQL Tutorial

Mingg`s Diary

밍구

공부 목적 블로그