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

SQL Any, All

SQL Insert Select

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