SQL EXISTS CLAUSE

From WikiOD

EXISTS CLAUSE[edit | edit source]

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[edit | edit source]

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[edit | edit source]

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

Result

Id FirstName LastName
1 Ozgur Ozturk

Purpose[edit | edit source]

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)

Credit:Stack_Overflow_Documentation