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 |
