WHERE EXISTS tests if a subquery returns any records.
EXISTS returns true if the subquery returns one or more records.
EXISTS is commonly used with correlated subqueries.
List customers with orders over $5000.
SELECT *
FROM Customer
WHERE EXISTS
(SELECT Id
FROM [Order]
WHERE CustomerId = Customer.Id
AND TotalAmount > 5000)
Note: This is a correlated subquery with Customer.Id in the inner query referencing the outer query.
Id | FirstName | LastName | City | Country | Phone |
---|---|---|---|---|---|
7 | Frédérique | Citeaux | Strasbourg | France | 88.60.15.31 |
20 | Roland | Mendel | Graz | Austria | 7675-3425 |
24 | Maria | Larsson | Bräcke | Sweden | 0695-34 67 21 |
32 | Howard | Snyder | Eugene | USA | (503) 555-7555 |
34 | Mario | Pontes | Rio de Janeiro | Brazil | (21) 555-0091 |
![]() |
EXISTS syntax.
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 |
SELECT CompanyName
FROM Supplier
WHERE EXISTS
(SELECT ProductName
FROM Product
WHERE SupplierId = Supplier.Id
AND UnitPrice > 100)
Note: This is also a correlated subquery with Supplier.Id referencing the outer query.
CompanyName |
---|
Plutzer Lebensmittelgroßmärkte AG |
Aux joyeux ecclésiastiques |