HTML tutorial
CSS3 tutorial
Bootstrap tutorial
JavaScript tutorial
JQuery tutorial
AngularJS tutorial
React tutorial
NodeJS tutorial
PHP tutorial
Python tutorial
Python3 tutorial
Django tutorial
Linux tutorial
Docker tutorial
Ruby tutorial
Java tutorial
C tutorial
C ++ tutorial
Perl tutorial
JSP tutorial
Lua tutorial
Scala tutorial
Go tutorial
ASP.NET tutorial
C # tutorial
The EXISTS operator is used to test for the existence of any record in a subquery
The EXISTS
operator is used to test for the existence of any record in a subquery.
The EXISTS
operator returns TRUE if the subquery returns one or more records.
EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE
EXISTS
(SELECT column_name FROM table_name WHERE
condition);
Below is a selection from the "Products" table in the Northwind sample database:
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 |
And a selection from the "Suppliers" table:
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 |
The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM
Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
The following SQL statement returns TRUE and lists the suppliers with a product price equal to 22:
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM
Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);