SQL WHERE EXISTS

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.

Example

#

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.

Result:  15 records
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

Syntax

EXISTS syntax.

SELECT column-names
  FROM table-name
 WHERE EXISTS 
      (SELECT column-name
         FROM table-name
        WHERE condition)

More Examples

EXISTS Subquery

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Find suppliers with products over $100.
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.

Result: 2 records
CompanyName
Plutzer Lebensmittelgroßmärkte AG
Aux joyeux ecclésiastiques

You may also like



Guides


vsn 3.1