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 :
EXISTSshould be used to check if a value exist in another tableINshould be used for static listJOINshould be used to retrieve data from other(s) table(s)