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.
The EXISTS condition 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 |