“`html

SQL assists in locating particular details within databases. One method to retrieve specific details is by eliminating unnecessary items. The NOT IN operator serves as an ideal tool for sifting through extensive information when you aim to retain only the elements that do not fulfill particular criteria, thus emphasizing the utility of the NOT IN operator for this purpose. It aids in the exclusion of irrelevant data. This article will educate you about the NOT IN operator, present its alternatives, and assist you in circumventing frequent issues.

Table of Contents:

• What is the NOT IN Operator in SQL?
• Reasons to Utilize the NOT IN Clause in SQL
• Employing NOT IN to Omit Specific Values in SQL
• Utilizing NOT IN with Subqueries in SQL
• Alternative Approaches to the SQL NOT IN Operator
• Using NOT EXISTS in SQL
• Employing LEFT JOIN in SQL
• Frequently Encountered Errors and Solutions
• Performance Analysis of NOT EXISTS Compared to LEFT JOIN
• Optimal Practices
• Practical Examples
• Final Thoughts
• Using NULL Values in the NOT IN Clause in SQL – FAQs

What is the NOT IN Operator in SQL?

The NOT IN operator in SQL verifies whether the values of a column are absent from a specified list or subquery. If a match is found, the corresponding row is disregarded.

Syntax:

SELECT column1, column2, ...

FROM table_name

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

-- Or using 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 the 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 appears once created and populated with values.

-- Create a table for 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 ExcludedDepartments table appears after its creation and value insertion.

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

SELECT EmployeeName

FROM Employees

WHERE Department NOT IN (SELECT Department FROM ExcludedDepartments);

Output:

Explanation: This query retrieves Employees whose departments are not listed in the ExcludedDepartments table by comparing each employee’s department with the excluded departments.

Why Utilize the NOT IN Clause in SQL?

The NOT IN operator enables you to extract rows that contain specific value sets while disregarding the remaining data.

Example: Identify products that are not found 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 appears subsequent to its creation and value entry.

“““html

Utilizing NOT IN to omit certain values in SQL

This approach illustrates a method for sorting data to exclude particular records.

Example:

-- retrieve products that are not part of "Electronics" or "Clothing" categories:

SELECT product_name, category

FROM Products

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

Output:

Explanation: In this instance, the query displays product names and categories that are not found in “Electronics” and “Clothing”.

Employing NOT IN with Subqueries in SQL

This technique describes how to apply NOT IN with subqueries to filter data based on the output of another query.

Example:

SELECT product_name, price

FROM Products

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

Output:

Explanation: This query provides product names and categories that do not belong to “Electronics” and “Clothing” using NOT IN with subqueries.

Alternative Approaches to the SQL NOT IN Operator

Alternative approaches are utilized to manage NULL values for enhanced efficiency. Typical alternatives include:

• NOT EXISTS
• LEFT JOIN/IS NULL

Applying NOT EXISTS in SQL

NOT EXISTS is generally a more efficient substitute for NOT IN, as it verifies the nonexistence of rows that meet a condition in a subquery. It provides improved performance while handling NULL values effectively.

Let’s define two tables named 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 the 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 the appearance of the products table after establishing 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 appears after creating and populating the values.

Example:

--Performing 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: This query produces employee names and their corresponding department that lack any records in SafetyTrainingCompletion.

Utilizing LEFT JOIN in SQL

The LEFT JOIN function is employed to return all rows from the left table alongside corresponding rows from the right table.

Example:

---Performing 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: This query applies LEFT JOIN to include all employees, even those who lack training records.

Frequent Error and Solution

It’s essential to exercise caution when utilizing NOT IN alongside NULL Values, as they do not function harmoniously.

Issue: Should the list or subquery contain NULL, the entire NOT IN condition might yield Unknown, resulting in unforeseen outcomes.

Resolution: Exclude the NULL values from your subquery or list.

Let’s clarify this with an illustration:

We possess a table of employees and a corresponding table of employees who have fulfilled a training course from Intellipaat, and we aim to identify the employees who haven’t finished the training. Nonetheless, some entries in the training table may include NULL values attributable to a data entry mistake.

--  Creating an employee table

CREATE TABLE Employees (

    employee_id INT PRIMARY KEY,

    employee_name VARCHAR(255),

    Department VARCHAR(255)

);

``````html

-- 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 showcase the output

Select * from Employees;

This depicts how the products table appears after establishing and inserting the values.

--Creating table TrainingCompletion (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 showcases how the products table looks after creating and inserting the values.

The Issue: NOT IN with NULL

SELECT employee_name

FROM Employees

WHERE employee_id NOT IN (SELECT employee_id FROM TrainingCompletion);

Expected Outcome: We anticipated seeing “Charlie Brown” and “Eve Wilson” since they were absent from the TrainingCompletion table.

Actual Outcome: The Query yielded no results.

What accounts for the discrepancy between Actual Output and Expected Output?

1. Nature of NULL: NULL is invariably utilized to denote empty values.
2. Impact of UNKNOWN: In SQL Server, the WHERE Clause, the UNKNOWN Condition effectively filters out rows, even those that would have otherwise matched. As a result, the presence of NULL in the NOT IN list renders the entire Condition Unknown for all records, leading to no rows being retrieved.

The Resolution: Excluding Nulls

To rectify this, we eliminate 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: The subquery retrieves all the EmployeeID with NOT NULL values, and the WHERE Clause excludes EmployeeID that are not present in the subquery.

Performance Assessment of NOT EXISTS and LEFT JOIN

Features	NOT EXISTS	LEFT JOIN
Overall Efficiency	NOT EXISTS tends to be superior for filtering data in large datasets, as it ceases searching once a match is located.	LEFT JOIN can be quite efficient with appropriate indexing.
Large DataSets	Demonstrates good performance on significant data sets.	The likelihood of a full table scan is not optimized.
Indexing	NOT EXISTS is less reliant on indexing.	LEFT JOIN is significantly reliant on indexing.
Readability	NOT EXISTS is regarded as more legible as it clearly indicates the absence of matching rows, rendering it straightforward to read.	LEFT JOIN is more intricate to interpret as it amalgamates tables that must filter for missing values, creating a two-step process.
Flexibility	NOT EXISTS is less adaptable as they are tailored for a specific purpose.	LEFT JOIN is more versatile as it pulls data from both tables even with absent matches.
NULL Management	Effectively manages NULLs within the subquery.	Relies on how NULLs are represented in JOIN.

Recommended Practices

• Be Cautious of Missing Data: NOT IN might yield incorrect results if your data contains empty values.
• Enhance Speed: If your data is structured with “Indexes”, NOT IN will perform significantly faster.
• Keep it Concise: NOT IN can hinder performance when dealing with large datasets.
• Verify Your Queries: Queries should be tested on a sample dataset prior to application on actual data.

Real-world Scenarios

1. E-commerce: This online retail platform allows customers to filter their orders by various 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 identifies and lists all orders that are not categorized as ‘Cancelled’ or ‘Delivered’ using the NOT IN clauses.

2. Healthcare Management: Patients can make appointments at the hospital for their check-ups.

-- 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
``````html
that are neither 'Completed' nor 'Cancelled'  

SELECT *  

FROM Appointments  

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

Output:

Clarification: In this instance, this query retrieves all appointments that are neither ‘Completed’ nor Cancelled.

Summary

The NOT IN clause is a robust SQL operator utilized to eliminate values based on specific criteria. When applied incorrectly with NULL values, it may yield inaccurate results. Whether you are cleansing data or refining reports, it’s quite straightforward, and comprehending NOT IN can enhance performance. By adhering to best practices, you can craft effective SQL queries.

To discover further about SQL functions, examine this SQL course and also delve into SQL Interview Questions curated by industry specialists.

How to Incorporate NULL Values within NOT IN Clause in SQL? – FAQs

The article How to Incorporate NULL Values within NOT IN Clause in SQL? first appeared on Intellipaat Blog.

“`