How to Use NULL Values Inside NOT IN Clause in SQL?

SQL helps to find specific information in databases. One way to find the specific information is to filter out things that we do not need. The NOT IN is a perfect operator for sorting through large information, when you only want to keep the things that do not match certain conditions, so that’s where the NOT IN operator helps you. It helps in filtering the unwanted data. This blog will guide you about NOT IN, show you its alternative, and help you avoid common problems.

Table of Contents:

• What is NOT IN Operator in SQL?
• Why Use the NOT IN Clause in SQL?
• Using NOT IN to exclude specific values in SQL
• Using NOT IN with Subqueries in SQL
• Alternative Methods to the SQL NOT IN Operator
• Using NOT EXISTS in SQL
• Using LEFT JOIN in SQL
• Common Error and Solution
• Performance Comparison of NOT EXISTS and LEFT JOIN
• Best Practices
• Real-world Examples
• Conclusion
• How to Use NULL Values Inside NOT IN Clause in SQL? – FAQs

What is NOT IN Operator in SQL?

The NOT IN operator in SQL checks whether a column’s values are not in a given list or subquery. If a value is found, the row is excluded.

Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE column_name NOT IN (value1, value2, ...);

-- Or with a subquery:

SELECT column1, column2, ...

FROM table_name

WHERE column_name NOT IN (SELECT column_name FROM another_table);

Example:

—---Create Table

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    EmployeeName VARCHAR(50),

    Department VARCHAR(50)

);

---Insert data into a table

INSERT INTO Employees (EmployeeID, EmployeeName, Department) VALUES

(1, 'Alice', 'Sales'),

(2, 'Bob', 'Marketing'),

(3, 'Charlie', 'Sales'),

(4, 'David', 'IT'),

(5, 'Eve', 'HR');

—---Display the table

Select * from Employees;

This is how the table looks once it is created and the values are inserted.

-- Create a table of excluded departments

CREATE TABLE ExcludedDepartments (

    Department VARCHAR(50)

);

--Insert into table

INSERT INTO ExcludedDepartments (Department) VALUES

('Sales'),

('HR');

--Display the output

Select * from ExcludedDepartments;

This is how the table ExcludedDepartments looks after creating and inserting the values.

--Use NOT IN to find employees not in excluded departments

SELECT EmployeeName

FROM Employees

WHERE Department NOT IN (SELECT Department FROM ExcludedDepartments);

Output:

Explanation: Here, this query finds the Employees whose departments are not present in the ExcludedDepartments table by comparing each employee’s department against the list of excluded departments.

Why Use the NOT IN Clause in SQL?

THE NOT IN operator helps you pull out rows that contain certain sets of values while ignoring the rest of the data.

Example: Find products that are not available in the “Electronics” or “Clothing” categories.

--Creating table 

CREATE TABLE Products (

    product_id INT PRIMARY KEY,

    product_name VARCHAR(255),

    category VARCHAR(255),

    price DECIMAL(10, 2),

    stock_quantity INT

);

--Inserting values

INSERT INTO Products (product_id, product_name, category, price, stock_quantity) VALUES

(1, 'Laptop', 'Electronics', 1200.00, 15),

(2, 'T-Shirt', 'Clothing', 25.00, 100),

(3, 'Cookbook', 'Books', 30.00, 50),

(4, 'Smartphone', 'Electronics', 800.00, 20),

(5, 'Jeans', 'Clothing', 50.00, 75),

(6, 'Novel', 'Books', 15.00, 120),

(7, 'Desk Lamp', 'Home Goods', 40.00, 60),

(8, 'Running Shoes', 'Sporting Goods', 70.00, 90),

(9, 'Coffee Maker', 'Home Goods', 60.00, 45),

(10, 'Yoga Mat', 'Sporting Goods', 25.00, 80);

Select * from Products;

Output:

This is how the products table looks after creating and inserting the values.

Using NOT IN to exclude specific values in SQL

This method describes a technique for filtering data to eliminate specific records.

Example:

-- find products that are not present in  "Electronics" or "Clothing" categories:

SELECT product_name, category

FROM Products

WHERE category NOT IN ('Electronics', 'Clothing');

Output:

Explanation: Here, this query shows product names and categories that are not in “Electronics” and “Clothing”.

Using NOT IN with Subqueries in SQL

This method explains how to use NOT IN with subqueries to filter data based on the results of another query.

Example:

SELECT product_name, price

FROM Products

WHERE price NOT IN (SELECT price FROM Products WHERE category = 'Electronics');

Output:

Explanation: Here, this query shows product names and categories that are not in “Electronics” and “Clothing” using NOT IN with subqueries.

Alternative Methods to the SQL NOT IN Operator

Alternative methods are used to handle NULL values for better performance. Common alternatives are:

• NOT EXISTS
• LEFT JOIN/IS NULL

Using NOT EXISTS in SQL

NOT EXISTS is usually a more efficient alternative to NOT IN, and it checks the absence of rows matching a condition in a subquery. It offers better performance and handles NULL values.

Let us create two tables called Employees and SafetyTrainingCompletion.

--Create table employees

CREATE TABLE Employees (

    employee_id INT PRIMARY KEY,

    employee_name VARCHAR(255),

    department VARCHAR(255),

    job_title VARCHAR(255)

);

--Insert data into a table

INSERT INTO Employees (employee_id, employee_name, department, job_title) VALUES

(1, 'John Doe', 'Engineering', 'Software Engineer'),

(2, 'Jane Smith', 'HR', 'HR Manager'),

(3, 'Robert Brown', 'Sales', 'Sales Representative'),

(4, 'Emily Davis', 'Engineering', 'Data Scientist'),

(5, 'Michael Wilson', 'Marketing', 'Marketing Analyst'),

(6, 'Jessica Garcia', 'Finance', 'Accountant');

--To display the output

Select * from Employees;

This is how the products table looks after creating and inserting the values.

--Create SafetyTrainingCompletion Table

CREATE TABLE SafetyTrainingCompletion (

    completion_id INT PRIMARY KEY,

    employee_id INT,

    completion_date DATE,

    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)

);

---Inserting values into the table

INSERT INTO SafetyTrainingCompletion (completion_id, employee_id, completion_date) VALUES

(101, 1, '2023-11-01'),

(102, 2, '2023-11-02'),

(103, 4, '2023-11-03');

--Display the table

Select *from SafetyTrainingCompletion;

This is how the products table looks after creating and inserting the values.

Example:

--Executing the NOT EXISTS

 Query:

SELECT employee_name, department

FROM Employees e

WHERE NOT EXISTS (

    SELECT 1

    FROM SafetyTrainingCompletion st

    WHERE e.employee_id = st.employee_id

);

Output:

Explanation: Here, this query returns employee names and their respective department that do not have any records in SafetyTrainingCompletion.

Using LEFT JOIN in SQL

The LEFT JOIN is used to return all rows from the left table and matching rows from the right table.

Example:

---Executing LEFT JOIN

SELECT

    e.employee_name,

    e.department

FROM

    Employees e

LEFT JOIN

    SafetyTrainingCompletion st ON e.employee_id = st.employee_id

WHERE

    st.completion_date IS NULL; 

Output:

Explanation: Here, this query uses LEFT JOIN to include all employees, even those without training records.

Common Error and Solution

You need to be careful when using the NOT IN and NULL Values. They do not work together.

Problem: If the list or subquery contains NULL, the entire NOT IN condition might return Unknown, leading to unexpected results.

Solution: Filter out the NULL values in your subquery or list.

Let us understand this with an example:

We have a table of employees and a table of employees who have completed a training course from Intellipaat, and finding the employees who haven’t completed the training. However, some records in the training table may contain NULL values due to a data entry error.

--  Creating an employee table

CREATE TABLE Employees (

    employee_id INT PRIMARY KEY,

    employee_name VARCHAR(255),

    Department VARCHAR(255)

);

-- Inserting values 

INSERT INTO Employees (employee_id, employee_name, department) VALUES

(1, 'Alice Smith', 'HR'),

(2, 'Bob Johnson', 'IT'),

(3, 'Charlie Brown', 'Sales'),

(4, 'David Lee', 'Marketing'),

(5, 'Eve Wilson', 'Finance');

-- To display the output

Select * from Employees;

 This is how the products table looks after creating and inserting the values.

--Creating table TrainingCompletion table (with NULL values)

CREATE TABLE TrainingCompletion (

    completion_id INT PRIMARY KEY,

    employee_id INT,

    completion_date DATE,

    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)

);

INSERT INTO TrainingCompletion (completion_id, employee_id, completion_date) VALUES

(101, 1, '2023-10-26'),

(102, 2, '2023-10-27'),

(103, 4, '2023-10-29'),

(104, NULL, '2023-10-30'); -- Intentional NULL value

Select * from TrainingCompletion;

This is how the products table looks after creating and inserting the values.

The problem: NOT IN with NULL

SELECT employee_name

FROM Employees

WHERE employee_id NOT IN (SELECT employee_id FROM TrainingCompletion);

Expected Result: We expected to see “Charlie Brown” and “Eve Wilson” since they were not present at the TrainingCompletion table.

Actual Result: The Query returned no rows.

Why is there a difference between Actual Output vs Expected Output?

1. NULL’s Nature: NULL is always used to represent empty values.
2. UNKNOWN’s Effect: In SQL Server, the WHERE Clause, UNKNOWN Condition effectively filters out rows, even those that would match. Therefore, the presence of NULL in the NOT IN list makes the whole Condition Unknown for all records, resulting in no rows being returned.

The Solution: Filtering out Nulls

To fix this, we filter out NULL from  the subquery:

SELECT employee_name

FROM Employees

WHERE employee_id NOT IN (SELECT employee_id FROM TrainingCompletion WHERE employee_id IS NOT NULL);

Output:

Explanation: Here, the subquery returns all the EmployeeID with NOT NULL values, and the WHERE Clause filters out EmployeeID that is not in the subquery.

Performance Comparison of NOT EXISTS and LEFT JOIN

Best Practices 

• Watch out for missing Data: NOT IN might give you the wrong results if your data contains blank values
• Make it Faster: If your data is organized with “Indexes”, NOT IN will work much quicker.
• Keep it short: NOT IN can slow down performance if you are working on large data.
• Test your queries: The queries have to be checked on a sample dataset before being used on actual data.

Real-world Examples

1. E-commerce: This e-commerce site allows customers to filter their orders by multiple statuses such as ‘Pending’, ‘Shipped’, and ‘Delivered’.

Example:

-- Create Orders Table  

CREATE TABLE Orders (OrderID INT, CustomerName VARCHAR(50), Status VARCHAR(20));  

-- Insert Sample Data  

INSERT INTO Orders VALUES (1, 'Anand', 'Pending'),  

                          (2, 'John', 'Shipped'),  

                          (3, 'Vijay', 'Delivered'),  

                          (4, 'Joseph', 'Cancelled');  

-- Display all the items in the list  that are not in the 'Cancelled' or 'Delivered'

SELECT *

FROM Orders

WHERE Status NOT IN ('Cancelled', 'Delivered');

Output:

Explanation: This query finds and lists all the orders that are not in ‘Cancelled’ or ‘Delivered’ with the help of NOT IN clauses.

2. Healthcare Management: Patients can book their appointments in the hospital for their checkups

-- Creating  Table  

CREATE TABLE Appointments (

    AppointmentID INT,

    PatientName VARCHAR(50),

    Status VARCHAR(20)

);  

-- Inserting  Data  

INSERT INTO Appointments VALUES 

(1, 'Ravi', 'Scheduled'),  

(2, 'Meera', 'Completed'),  

(3, 'Sunil', 'No Show'),  

(4, 'Priya', 'Cancelled');  

-- Display all appointments that are not 'Completed' or 'Cancelled'  

SELECT *  

FROM Appointments  

WHERE Status NOT IN ('Completed', 'Cancelled');

Output:

Explanation: Here, this query gives all appointments that are not ‘Completed’ or Cancelled.

Conclusion

The NOT IN clause is a powerful SQL operator used to exclude values depending on conditions. When used incorrectly with NULL values, it can lead to incorrect results. Whether you are cleaning data or filtering reports, it is very easy, and understanding NOT IN may help to improve performance. By following best practices, you can write powerful SQL queries.

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

How to Use NULL Values Inside NOT IN Clause in SQL? – FAQs