< Sql Tutorial

Sql Not IN

← Previous Next →

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

← Previous Next →