SQL NOT IN
The NOT IN operator is used when you want to retrieve a column that has no entries in the table or referencing table.
This is important for keywords when we want to find records to management questions such as:
Which customer has not done any transaction with us?
Which product is not selling in the past few days?
For Example: If you want to find the names of customers who have not done any transactions
A customer table will be containing records of all the customers and the transaction table keeps the records of any transaction between the store and the customer.
Customers SQL table contains the following:
| Cust_id | first_name | last_name |
| 01 | Jhon | Cramer |
| 02 | Mathew | George |
| 03 | Phillip | McCain |
| 04 | Andrew | Thomas |
Transaction table contains:
| Transaction_ID | Cust_id | Product_ID | Amount | subject |
| 01 | 01 | 02 | 10 | 5.99 |
| 02 | 03 | 01 | 12 | 6.59 |
| 03 | 01 | 05 | 09 | 8.99 |
| 04 | 01 | 04 | 18 | 6.59 |
| 05 | 03 | 02 | 15 | 5.99 |
The NOT IN query would be like:
Select first_name, last_name, cust_id from customer where cust_id NOT IN ( Select cust_id from transactions)
The output will be:
| first_name | last_name | Cust_id |
| Mathew | George | 02 |
| Andrew | Thomas | 04 |
Thus:These are the 2 names of customers who have not done any transactions
