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.
  • EXISTS 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


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas