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 ‘w3wiki’ and ‘courses’.
Table 1: ‘w3wiki’
Creating a fable name w3wiki:
CREATE TABLE w3wiki(
id int PRIMARY KEY,
name varchar(100),
total_score int
);
Inserting data to table w3wiki:
INSERT INTO w3wiki(id,name,total_score)
VALUES(01,'Vishu',100);
INSERT INTO w3wiki(id,name,total_score)
VALUES(02,'Neeraj',95);
INSERT INTO w3wiki(id,name,total_score)
VALUES(03,'Aayush',85);
INSERT INTO w3wiki(id,name,total_score)
VALUES(04,'Vivek',70);
Output :
Table – w3wiki
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 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.