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.