NOT IN vs NOT EXISTS in SQL

Structured Query Language (SQL) is a domain-specific language used in managing and manipulating data in a relational database. In SQL, we use these two operators i.e. NOT IN and NOT EXISTS to filter out and efficiently retrieve our data from a table.

Both of these operators are negations of IN and EXISTS operators respectively. In short, they perform exact opposite types of operations compared to IN and EXISTS operators.

In this article, we are going to compare NOT IN and NOT EXISTS operators. We are going to discuss their syntax, working, and differences between them. We will also see examples related to them.

Creating Demo Tables

Before directly moving to the NOT IN and NOT EXISTS, we have to create two tables in our database. We going to create two tables, with the names ‘geeksforgeeks’ and ‘courses’.

Table 1: ‘geeksforgeeks’

Creating a fable name geeksforgeeks:

CREATE TABLE geeksforgeeks(
  id int PRIMARY KEY,
  name varchar(100),
  total_score int
 );

Inserting data to table geeksforgeeks:

INSERT INTO geeksforgeeks(id,name,total_score)
VALUES(01,'Vishu',100);
INSERT INTO geeksforgeeks(id,name,total_score)
VALUES(02,'Neeraj',95);
INSERT INTO geeksforgeeks(id,name,total_score)
VALUES(03,'Aayush',85);
INSERT INTO geeksforgeeks(id,name,total_score)
VALUES(04,'Vivek',70);

Output :

Table – geeksforgeeks

Table 2: ‘courses’

Creating a fable name courses:

CREATE TABLE courses(
  id int,
  course varchar(100),
 );

Inserting data to table courses:

INSERT INTO courses(id, course)
VALUES(01,'Python');
INSERT INTO courses(id, course)
VALUES(02,'Python');
INSERT INTO courses(id, course)
VALUES(01,'Java');
INSERT INTO courses(id, course)
VALUES(04, 'JavaScript');
INSERT INTO courses(id, course)
VALUES(02, 'C language');

Output:

Table – courses

NOT IN Operator

NOT IN operator is a negation operator that results in the rows which do not contain the specified values. We can clearly say that it performs tasks exact opposite of the IN operator. It can be used to filter out the data resulting from the subquery. Suppose we specified a list that contains id’s 101, 102, and 103. NOT IN operator returns all the rows that do not have these id’s present in them.

Syntax of NOT IN operator:

SELECT column01, column02,.......
FROM table_name
WHERE column_name NOT IN ( list of values)

Example of NOT IN Operator

Let’s display all the values from ‘geeksforgeeks’ table which do not have id’s in our specified list.

Query:

SELECT *
FROM geeksforgeeks
WHERE id NOT IN (2,4)

Output:

geeksforgeeks – NOT IN (2,4)

Explanation: In the above example, we have performed a query with NOT IN operator. We have specified a list i.e. (2,3) and displayed all the values from table geeksforgeeks which do not contain those values in their id column.

NOT EXISTS Operator

In SQL, NOT EXISTS Operator return boolean values. It is used for testing that if any records exists or not. If the any subquery do not results any values or return 0 then only the parent query will execute. In simple words, if the child query returns 0 then only the parent query will execute.

Syntax of NOT EXISTS Operator:

SELECT column01, column02,..........
FROM table_name
WHERE  NOT EXISTS (subquery);

Example of NOT EXISTS Operator

In this example, we are going to display all the rows of the geeksforgeeks table where there should not exist any record where the id of the geeeksforgeeks table is equal to id of the courses table and course is ‘DSA’.

Query:

SELECT id,name
FROM geeksforgeeks 
WHERE NOT EXISTS (SELECT  1  FROM courses 
    WHERE courses.id = geeksforgeeks.id and courses.course = 'DSA' );

Output:

geeksforgeeks – NOT EXISTS

Explanation: In the above query, we have specified in a subquery that there should not be any course named ‘DSA’ in our courses table. If there are not any courses that means our subquery will return 0. Therefore, NOT EXISTS, being a negation operator will display all the rows from geeksforgeeks table.

Differences between NOT IN and NOT EXISTS, Operators

In this, we are going to list the difference between these two operators i.e. NOT IN and NOT EXISTS.

SL No.	NOT IN Operator	NOT EXISTS Operator
1.	NOT IN operator is used to search data from a finite list of values or a subquery.	NOT EXISTS operator is used to evaluate a subquery and return returns true if the specified row is absent.
2.	NULL values creates problem in execution.	NULL values are not an exception here. It can easily compare with NULL values.
3.	It is considered less efficient for large data sets as it will search whole data set before returning any boolean value.	It is more efficient for large dataset. It stops execution when it finds the first matching row.
4.	Checks for existence of a values from a finite list. It return true if the matched values is not present in the given list.	Checks presence of an subquery. It returns true if the specified subquery return false.
5.	Negation of IN operator.	Negation of EXISTS operator.
6.	Syntax: WHERE column NOT IN ( list of values)	Syntax: WHERE EXISTS (subquery);

Conclusion

Although NOT IN and NOT EXISTS seem to be pretty much similar in terms of working, there is a vast difference in them. NOT IN operator is preferred for finite list of values whereas, NOT EXISTS operator takes less time and becomes more efficient when it comes to large datasets. Both of these operators are the negation of IN and EXISTS operators respectively. We have covered syntax and examples of both NOT IN and NOT EXISTS operators. We have also discussed how they are different from each other.