SQL

EXISTS CLAUSE

EXISTS CLAUSE

Customer Table

Id FirstName LastName
1 Ozgur Ozturk
2 Youssef Medi
3 Henry Tai

Order Table

Id CustomerId Amount
1 2 123.50
2 3 14.80

Get all customers with a least one order

SELECT * FROM Customer WHERE EXISTS (
    SELECT * FROM Order WHERE Order.CustomerId=Customer.Id
)

Result

Id FirstName LastName
2 Youssef Medi
3 Henry Tai

Get all customers with no order

SELECT * FROM Customer WHERE NOT EXISTS (
    SELECT * FROM Order WHERE Order.CustomerId = Customer.Id
)

Result

Id FirstName LastName
1 Ozgur Ozturk

Purpose

EXISTS, IN and JOIN could sometime be used for the same result, however, they are not equals :

  • EXISTS should be used to check if a value exist in another table
  • IN should be used for static list
  • JOIN should be used to retrieve data from other(s) table(s)

This modified text is an extract of the original Stack Overflow Documentation created by the contributors and released under CC BY-SA 3.0 This website is not affiliated with Stack Overflow