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)