SQL WHERE EXISTS Statement
What does WHERE EXISTS do?
- WHERE EXISTS tests for the existence of any records in a subquery.
- EXISTS returns true if the subquery returns one or more records.
- EXISTS is commonly used with correlated subqueries.
The SQL EXISTS syntax
The general syntax is
SELECT column-names
FROM table-name
WHERE EXISTS
(SELECT column-name
FROM table-name
WHERE condition)
| SUPPLIER |
|---|
| Id |
| CompanyName |
| ContactName |
| City |
| Country |
| Phone |
| Fax |
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
SQL EXISTS
Example
Problem: Find suppliers
with products over $100.
with products over $100.
SELECT CompanyName
FROM Supplier
WHERE EXISTS
(SELECT ProductName
FROM Product
WHERE SupplierId = Supplier.Id
AND UnitPrice > 100)
Note: This is a correlated subquery because the subquery references the enclosing query (with Supplier.Id).
Result: 2 records
| CompanyName |
|---|
| Plutzer Lebensmittelgroßmärkte AG |
| Aux joyeux ecclésiastiques |
in Austin, Texas