SQL WHERE EXISTS Statement ~ niit post

• WHERE EXISTS tests for the existence of any records in a subquery.
• EXISTS returns true if the subquery returns one or more records.
• EXISTS is commonly used with correlated subqueries.

The SQL EXISTS syntax

The general syntax is: 

1.  SELECT column-names

2.    FROM table-name

3.   WHERE EXISTS 

4.        (SELECT column-name

5.           FROM table-name

6.          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. 

1.  SELECT CompanyName

2.    FROM Supplier

3.   WHERE EXISTS

4.         (SELECT ProductName

5.            FROM Product

6.           WHERE SupplierId = Supplier.Id 

7.             AND UnitPrice > 100)       

This is a correlated subquery because the subquery references the enclosing query (with Supplier.Id). 

Results: 2 records 

CompanyName

Plutzer Lebensmittelgroßmärkte AG

Aux joyeux ecclésiastiques