The NOT IN and NOT EXISTS conditions are used to determine whether the records match based on the conditions. When working on SQL, we often need to filter the records that do not match the conditions defined. This can be achieved by using NOT IN and NOT EXISTS clauses. There are some key differences between NOT IN and NOT EXISTS. Let’s see the differences in this article.
Table of Contents:
Creating Sample Table
Let’s create a dataset to work on NOT IN and NOT EXISTS methods in SQL.
-- Create a students table
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50) NOT NULL,
course_name VARCHAR(50) -- Can be NULL
);
Let’s insert data in this student table:
-- Insert sample data
INSERT INTO students (student_id, student_name, course_name) VALUES
(1, 'Tarun', 'Datascience'),
(2, 'Baskar', 'Artificial Intelligence'),
(3, 'Cheran', 'Machine Learning'),
(4, 'Kadhir', 'Cloud Computing'),
(5, 'Hema', NULL); -- Emma has not chosen a course
SELECT * FROM students;
Output: This is how the students table looks after creating and inserting the values.
NOT IN Operator in SQL
The NOT IN operator in SQL is used to filter the matched data based on conditions. NOT IN works directly by comparing the values against a set of conditions. This will remove all the records that have NULL values. NOT IN has logic: TRUE, FALSE.
Example 1: Using NOT IN Operator
To find students who have not chosen a course from a specific list
SELECT *
FROM students
WHERE course_name NOT IN ('Datascience', 'Artificial Intelligence')
Output:
Explanation: This retrieved the data of students who have not enrolled in Datascience and Artificial Intelligence by using NOT IN.
Example 2: Using NOT IN with a Subquery
To find students whose course chosen is NOT available in the courses table.
CREATE TABLE available_courses (
course_name VARCHAR(50) PRIMARY KEY
);
-- Insert available courses
INSERT INTO available_courses (course_name) VALUES
('Datascience'),
('Artificial Intelligence'),
('Cloud Computing');
SELECT *
FROM students
WHERE course_name NOT IN (SELECT course_name FROM available_courses);
Output:
Explanation: This uses the subquery FROM available_courses to get the data of students whose chosen course is not available in the course list.
NOT EXISTS Operator in SQL
A NOT EXISTS operator will evaluate row by row and stop when a match is found. NOT EXISTS can handle NULL values and produce TRUE or FALSE based on the matches found.
Example 1: Using NOT EXISTS Operator
To fetch data from the student who has chosen a Machine Learning course
SELECT *
FROM students s
WHERE NOT EXISTS (
SELECT 1
FROM available_courses ac
WHERE s.course_name = ac.course_name
);
Output:
Explanation: This query returns with the student (Kadhir) who has chosen the course Machine Learning and the NULL where Hema doesn’t select any course.
Example 2:
To fetch the data of who has chosen the course but didn’t enroll and the data of who didn’t choose the course as well as not enrolled.
CREATE TABLE enrolled_students (
student_id INT PRIMARY KEY );
);
INSERT INTO enrolled_students (
student_id) VALUES
(1), -- Tarun enrolled
(2), -- Baskar enrolled
(4); -- Kadhir enrolled
SELECT *
FROM students s
WHERE NOT EXISTS (
SELECT 1
FROM enrolled_students e
WHERE s.student_id = e.student_id
);
Output:
Explanation: We updated the query with students who enrolled in the course after choosing the course. With that data, we can fetch the data of students who chose the course but didn’t enroll, and the student didn’t choose the course yet. Here, we can see that Cheran chose the machine learning course but hasn’t enrolled in it, and Hema didn’t even choose the course.
Difference between NOT IN and NOT EXISTS Operator in SQL
| NOT IN | NOT EXISTS |
| Filters data that does not exist in a subquery or list | It checks the existence of rows rather than values |
| It will not handle NULL values. If there are no values in a row, it may return with no results or an error. | It handles NULL values more efficiently. If there is no value in a row, it will return the NULL value. |
| It will compare values based on a set of values provided. | Evaluate row by row and stop when the match is found. |
| It is less efficient than NOT EXISTS, as it may return an error if there is no value in a row. | More efficient than NOT IN as it can handle NULL values. |
| It can only be used in a small dataset with values in it. | It works faster with large datasets and can use indexes efficiently. |
| Syntax:SELECT column_name(s)FROM table_nameWHERE column_name NOT IN (subquery); | Syntax:SELECT column_name(s)FROM table_name tWHERE NOT EXISTS (subquery); |
Advantages and Disadvantages of NOT IN and NOT EXISTS Operator in SQL
Advantages:
| NOT IN | NOT EXISTS |
| Easy to understand | Handles null values well. |
| Good for small datasets: works well with small data sets. | Faster on larger datasets and more efficient when using indexes. |
| Performs well if the subquery has limited and clean datasets. | Better performance when matching and stops when the match is found. |
Disadvantages:
| NOT IN | NOT EXISTS |
| If the subquery has NULL, we cannot expect it to return results. | More complex and slightly difficult to understand than NOT IN. |
| Handling large datasets is difficult | Can be slower when there are no indexes present. |
| It requires storing the tables before comparing them. | It has a complex query structure, so the database may optimize the query differently. |
Real-world Examples for NOT IN Operator in SQL
Example 1: Find products not sold in specific regions
-- Create a products table
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL
);
-- Insert sample data into products table
INSERT INTO products (product_id, product_name) VALUES
(1, 'Laptop'),
(2, 'Phone'),
(3, 'Tablet'),
(4, 'Headphones');
-- Create a sales table
CREATE TABLE sales (
product_id INT,
region VARCHAR(50),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- Insert sample data into the sales table
INSERT INTO sales (product_id, region) VALUES
(1, 'Southern'),
(3, 'Eastern'),
(4, 'Northern');
SELECT product_name
FROM products
WHERE product_id NOT IN (
SELECT product_id
FROM sales
WHERE region IN ('Northern', 'Eastern')
);
Output:
Explanation: The NOT IN command fetched the records of products that were not sold in the northern and eastern parts.
Example 2: Find employees who have not taken specific training courses
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50) NOT NULL
);
INSERT INTO employees (employee_id, employee_name) VALUES
(1, 'Adhav'),
(2, 'Neha'),
(3, 'Juli'),
(4, 'Arnav'),
(5, 'Lithin');
CREATE TABLE training_sessions (
employee_id INT,
training_name VARCHAR(100),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
INSERT INTO training_sessions (employee_id, training_name) VALUES
(1, 'Leadership Training'),
(4, 'Time Management Training'),
(3, 'Human Resource management');
SELECT employee_name
FROM employees
WHERE employee_id NOT IN (
SELECT employee_id
FROM training_sessions
WHERE training_name IN ('Leadership Training', 'Time Management Training')
);
Output:
Explanation: The NOT IN command fetched the data of employees who have not registered in any of the training.
Real-world Examples of NOT EXISTS Operator in SQL
Example 1: Find Customers who have not made any purchases
-- Create customers table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL
);
-- Insert sample data into customers table
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Sana'),
(2, 'Tanishq'),
(3, 'Pratap'),
(4, 'Kinshuk');
-- Create orders table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- Insert sample data into orders table
INSERT INTO orders (order_id, customer_id, order_date) VALUES
(101, 1, '2025-01-15'),
(102, 2, '2025-01-20');
SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE c.customer_id = o.customer_id
);
Output:
Explanation: The NOT EXISTS condition retrieves the data of customers who did not order anything.
Example 2: Find students who have not registered for any courses
-- Create a students table
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50) NOT NULL
);
-- Insert sample data into students table
INSERT INTO students (student_id, student_name) VALUES
(1, 'Elango'),
(2, 'Hari'),
(3, 'Sri'),
(4, 'Parth');
-- Create a registrations table
CREATE TABLE registrations (
student_id INT,
course_name VARCHAR(50),
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
-- Insert sample data into the registrations table
INSERT INTO registrations (student_id, course_name) VALUES
(1, 'Intellipaat_AI/ML_course'),
(3, 'Intellipaat_Datascience_course');
SELECT student_name
FROM students s
WHERE NOT EXISTS (
SELECT 1
FROM registrations r
WHERE s.student_id = r.student_id
);
Output:
Explanation: The NOT EXISTS fetched the data of students who were not registered for any of the Intellipaat courses.
Conclusion
Both NOT IN and NOT EXISTS filter records but work differently. NOT IN is simpler but fails with NULL values, while NOT EXISTS handles NULL efficiently. Performance-wise, NOT EXISTS is better as it uses indexes and stops at the first match, making it faster and more scalable. For better efficiency, NOT EXISTS is the preferred choice.