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

 

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

Doctor Table

Bill Table

Laboratory Table

 

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

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:

 

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:

 

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 31^st 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 1^st February 2000 to 10^th February 2000

OUTPUT:

 

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:

 

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:

 

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

 

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

 

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:

 

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]