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.

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

SQL WHERE EXISTS Statement

What does WHERE EXISTS do?

The SQL EXISTS syntax

SQL EXISTS Example

SQL EXISTS
Example