SQL NOT IN Operator Explained [8 Practical Examples]

Overview of SQL NOT IN Operator

The SQL Server NOT IN operator is used to replace a group of arguments using the <> (or !=) operator that is combined with an AND. It can make code easier to read and understand for SELECT, UPDATE or DELETE SQL commands,

The NOT IN operator works by comparing one value, usually a column, to a comma-separated list of potential match values held in a set of parentheses, The value to the left of the NOT IN operator is then compared, one at a time, to the entire list and an exact match to any one member of the list will cause the argument to evaluate as false

Advertisement

 

SQL NOT IN Syntax

{WHERE | HAVING | {AND | OR}} expression | column_name NOT IN ({comp_value1, comp_value2[, ...] );
Or
{WHERE | HAVING | {AND | OR}} expression | column_name NOT IN ({subquery});

Here,

  • Expression | column_name: It is an expression or column name which value is going to match in the list
  • {WHERE | HAVING | {AND | OR}} value: SQL IN works with either the WHERE or the HAVING clause, to specified multiple conditions with WHERE and HAVING clause we can use AND or OR logical operators
  • NOT: Used to exclude the defined multiple values in a WHERE clause condition
  • comp_value1, comp_value2...| com_valueN: List of comparative values within the parentheses separated by commas
  • Subquery: It is a Select subquery that returns one or more values of a compatible data type of the main query

 

SQL NOT IN examples

Consider hospital database with four tables patient, doctor, laboratory and bill tables for performing practical example
Patient Table

patient_idnameagegenderaddressdiseasedoctor_id
1reema23femalealthan,Suratfever21
2kusum50femalevadodaraheart failure22
3carlin43malevapiinfection23
4rahul26malenavsaricancer21
6hansha55femalevapidiabetes22

Doctor Table

doctor_idnameagegenderaddress
21asif55malebaruch
22dhawal40maleSurat
23krishna39femaleSurat
24lissa35femaleNavsari
25leeba34femalebaruch
26vini33femaleSurat
27Dhiren32maleNavsari

Bill Table

bill_nopatient_iddoctor_idroom_chargeno_of_days
500513405004
500626004808
500838003403
500947808906
501034001
501112003001
501226001102
501333302101
501412303402

Laboratory Table

lan_nopatient_iddoctor_iddateamount
1012102-02-20004000
2022109-09-2001300
3032203-03-2001600
4012302-06-2002800
5042105-07-2003900
6022510-04-2004550
7042203-04-2005900

 

Example-1: SQL NOT IN with Numeric list of values

Write SQL query to display patient details whose age is not in-between range of 25 to 30

Advertisement
SELECT patient_id, name, age, gender, address, disease, doctor_id
FROM patient
WHERE  (age NOT IN (20, 21, 22, 23, 24, 25))

In this query SQL NOT IN operator is used to compare with the WHERE clause to fetch patient records who are not in the range of 20 to 25

OUTPUT:

SQL NOT IN Operator Explained [8 Practical Examples]

 

Example-2: SQL NOT IN with String list of values

Write SQL query to fetch patient records who are living in the city except for the Vapi and Vadodara

SELECT patient_id, name, age, gender, disease, city AS City
FROM  patient
WHERE (city NOT IN ('vapi', 'vadodara'))

In the previous query, SQL NOT IN operator is applied on column name city to compare city column name values of table patient with Vapi and Vadodara, the records in which city name is not ‘Vapi’ and ‘Vadodara’ will be displayed in the output

OUTPUT:

SQL NOT IN Operator Explained [8 Practical Examples]

 

Example-3: SQL NOT IN with Date list of values

The NOT IN operator can be used to search for any date or date-time value except those matches that match one from a list of date or date-time values

Write SQL Query to display patient name with laboratory report details whose reporting date is not in between 1 to 10 date of January 2001 to 31st December 2001

SELECT patient.name, patient.disease, laboratory.lab_no, laboratory.date, laboratory.amount
FROM patient LEFT OUTER JOIN
laboratory ON patient.patient_id = laboratory.patient_id
WHERE (laboratory.date NOT IN ('01-02-2000', '02-02-2000', '03-02-2000', '04-02-2000', '05-02-2000', '06-02-2000', '07-02-2000', '08-02-2000', '09-02-2000', '10-02-2000'))

In the above query SQL NOT IN operator is applied on column name date of table laboratory to retrieve patient data who have not taken laboratory test in between the date of 1st February 2000 to 10th February 2000

Advertisement

OUTPUT:

SQL NOT IN Operator Explained [8 Practical Examples]

 

Example-4: SQL NOT IN with Subquery

Write SQL Query to display those doctor details who are not having any patient

SELECT doctor_id, name, age, gender, address
FROM doctor
WHERE(doctor_id NOT IN (SELECT doctor_id FROM patient))
  • In the above query, the condition is matched with the output of the subquery using the SQL NOT operator
  • The subquery will retrieve doctor IDs from the patient table, which will be used as the comparison list for the outer query where clause condition

OUTPUT:

SQL NOT IN Operator Explained [8 Practical Examples]

 

Example-5: SQL NOT IN with columns

All examples thus far we have seen one variable or column being compared to a list of hard-coded values, but it can be the other use of SQL NOT IN operator to compare a value with a list of column name values

Use SQL NOT IN to display patient billing details where either doctor charges or the room charges not having 300 value

SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days
FROM bill
WHERE (300 NOT IN (doctor_charge, room_charge))
  • In the previous query, the SQL NOT IN operator compares the list of column values with one constant value of 300
  • This query returns records from the bill table where both the column doctor charge or room charge is not having a value of 300

OUTPUT:

SQL NOT IN Operator Explained [8 Practical Examples]

 

Example-6: SQL NOT IN with Update statement

Modify doctor charges set to 700 where the charges are not in the range of 500 to 800 in the bill details table of patient

UPDATE bill SET doctor_charge = 700
WHERE (doctor_charge NOT IN (200, 300, 400, 500, 600, 700, 800, 900))
  • In this query, SQL NOT IN operator is applied to update the record of bill table where doctor charges are multiplication of 100 from 200 to 900
  • when we execute the above query four rows are updated in the bill table and modify the doctor charge 700
  • To see the result of the above query, SQL select query statement is used to fetch all records of bills

OUTPUT:

SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days
FROM     bill

SQL NOT IN Operator Explained [8 Practical Examples]

 

Example-7: SQL NOT IN with Delete statement

Write SQL query to remove patient bill record whose no of admitted days for less than 2 using SQL NOT IN

DELETE FROM bill
WHERE (no_of_days NOT IN (2, 3, 4, 5, 6, 7, 8, 9, 10))
  • In the above query of SQL delete statement, SQL NOT IN operator is applied on no. of days column value to compare with the list of values more than 1 day

OUTPUT:

To see the result of the above query, SQL select statement is used on table laboratory

SQL NOT IN Operator Explained [8 Practical Examples]

 

Example-8: SQL NOT IN with HAVING CONDITION

SQL NOT IN operator can also be used with SQL HAVING clause when the need to retrieve records by making group of rows using GROUP By clause

Write SQL query to fetch patient data with lab details based on the disease of an age group not in the range 20 to 25

SELECT patient.disease, patient.name, patient.age, laboratory.lab_no, laboratory.date
FROM patient LEFT OUTER JOIN laboratory ON patient.patient_id = laboratory.patient_id
GROUP BY patient.disease, patient.name, patient.age, laboratory.lab_no, laboratory.date
HAVING (patient.age NOT IN (20, 21, 22, 23, 24, 25))
  • In this query, SQL Group by clause is used to make a group of records based on diseases name and left outer join is applied to join two tables patient and laboratory
  • SQL IN operator is applied in the conditional statement HAVING with not the age list of 21 to 25 to be compare

OUTPUT:

SQL NOT IN Operator Explained [8 Practical Examples]

 

Summary

In this article on SQL, NOT IN, the overview of SQL NOT IN operator and its key point has been covered in first part, Syntax of SQL NOT IN, some key points for SQL NOT IN operator, then we have covered various examples of SQL NOT IN operator like SQL NOT IN with numeric list, SQL NOT IN with string list and date list, also covered SQL NOT IN with subquery and SQL NOT IN operator with the group by and having the condition

 

References

SQL operators
SQL select clause
SQL LEFT OUTER JOIN

 

What's Next

SQL IN Operator Explained [10 Practical Examples]

 

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment