Looking for sql Answers? Try Ask4KnowledgeBase
Looking for sql Keywords? Try Ask4Keywords

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)