W3Schools Learner's Blog

w3Schools programming knowledge summary website

div

1/03/2018

SQL NOT IN

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_idfirst_namelast_name
01JhonCramer
02MathewGeorge
03PhillipMcCain
04AndrewThomas
Transaction table contains:
Transaction_IDCust_idProduct_IDAmountsubject
010102105.99
020301126.59
030105098.99
040104186.59
050302155.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_namelast_nameCust_id
MathewGeorge02
AndrewThomas04
Thus:These are the 2 names of customers who have not done any transactions.

No comments:

Post a Comment

Note: only a member of this blog may post a comment.