SQL NOT IN

SQL NOT IN Opeartor

NOT IN operator in SQL is used when you need to retrieve records with specific columns that has no entries in the table or referencing table.

NOT IN Syntax

SELECT column_name1, column_name2, .... , column_nameN
FROM table_name
WHERE column_name1 NOT IN (value1, value2, ... , valueN);
value1, value2, ... , valueN can also be obtained by using SELECT statement as given in the example below.

Example 1

Consider a employee table as follows
  empno |    ename   | salary  
--------+------------+---------
1 | John | 2000.98
2 | Amanda | 3170.89
3 | Maggi | 4170.89
4 | Amar | 6170.89
5 | Shubham | 2000.98
The following sql query selects employees whose name is not present in the given list.
SELECT ename
FROM employee
WHERE ename NOT IN ('Maggi','Amanda');

Output

  ename
---------
John
Amar
Shubham
(3 rows)

Example 2

Following examples finds out Which customers has NOT done any transactions?
For this example, consider a "customer" table containing records of all the customers and "transaction" table containing records of any transaction between the store and the customer.

Customer Table:
C_id first_name last_name
01 Shubham Gade
02 Amar Waghmode
03 Chinmay Gaikwad
04 Sameera Thomas
05 Anushka Sharma

Transaction Table:
Trans_ID C_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

If you want to find the names of customers who haven't done any transactions the query would be:
Select first_name, last_name, c_id 
from customer
where cust_id NOT IN ( Select c_id from transaction;);
Output
first_name      last_name        C_id
------------ ---------- ---------
Amar Waghmode 02
Sameera Thomas 04
Anushka Sharma 05
There are 3 customers who haven't done any transactions.

The nested query select c_id from transaction; gets customer ids from "transaction" table. The main query states that select first name, last name and customer id of those customers from customer table whose id is not present in the result from nested query.

Prev - SQL Comparison Keywords

Next - SQL ORDER BY Clause

Comments