SQL EXISTS CLAUSE

Example

Customer Table

IdFirstNameLastName
1OzgurOzturk
2YoussefMedi
3HenryTai

Order Table

IdCustomerIdAmount
12123.50
2314.80

Get all customers with a least one order

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

Result

IdFirstNameLastName
2YoussefMedi
3HenryTai

Get all customers with no order

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

Result

IdFirstNameLastName
1OzgurOzturk

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)