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.

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.

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.
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
CompanyName
Plutzer Lebensmittelgroßmärkte AG
Aux joyeux ecclésiastiques

You may also like



Guides