Dofactory.com
Dofactory.com
Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.

SQL WHERE EXISTS

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

#

List customers with orders over $5000.

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

Note: This is a correlated subquery with Customer.Id in the inner query referencing the outer query.

Result:  15 records
Id FirstName LastName City Country Phone
7 Frédérique Citeaux Strasbourg France 88.60.15.31
20 Roland Mendel Graz Austria 7675-3425
24 Maria Larsson Bräcke Sweden 0695-34 67 21
32 Howard Snyder Eugene USA (503) 555-7555
34 Mario Pontes Rio de Janeiro Brazil (21) 555-0091

Syntax

EXISTS syntax.

SELECT column-names
  FROM table-name
 WHERE EXISTS 
      (SELECT column-name
         FROM table-name
        WHERE condition)

More Examples

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 with Supplier.Id referencing the outer query.

Result: 2 records
CompanyName
Plutzer Lebensmittelgroßmärkte AG
Aux joyeux ecclésiastiques

You may also like



Last updated on Dec 21, 2023

Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.