SQL WHERE EXISTS

SQL WHERE EXISTS Explained

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

#

Problem: List customers with orders over $5000.

copy

SELECT FirstName, LastName
  FROM Customer
 WHERE EXISTS
       (SELECT Id
          FROM [Order]
         WHERE CustomerId = Customer.Id 
           AND TotalAmount > 5000)

SELECT FirstName, LastName
  FROM Customer
 WHERE EXISTS
       (SELECT Id
          FROM [Order]
         WHERE CustomerId = Customer.Id 
           AND TotalAmount > 5000)

Note: This is a correlated subquery because Customer.Id in the inner query references the outer query.

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

#

SQL EXISTS Subquery

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued

Problem: Find suppliers with products over $100.

copy

SELECT CompanyName
  FROM Supplier
 WHERE EXISTS
       (SELECT ProductName
          FROM Product
         WHERE SupplierId = Supplier.Id 
           AND UnitPrice > 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 because with Supplier.Id the subquery references the enclosing query.

Result: 2 records