How to Use Not Exists in MySQL

This tutorial aims at understanding how to use the NOT EXISTS clause in the MySQL database.

Businesses and organizations must fetch all the tables to understand each one separately. It is critically important when a new individual or a data analyst joins the team.

Under such a situation, the company needs to fetch all tables simultaneously to demonstrate the working of each table and the reason behind the creation of each table in the database.

Once the analyst has access to the data, they can gather insights using certain data filtering techniques. There are multiple ways by which an individual can filter data in MySQL.

One of those techniques is using the NOT EXISTS clause.

Use the Not Exists Condition to Filter Data in MySQL

To understand this in greater depth, let us create a couple of tables in our existing database.

Then we will try to understand how to fetch data using the NOT EXISTS condition.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_details, along with a few rows.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int unsigned,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName)
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller"),
 (8,"A","SAan"),
 (9,"Santa","Merry"),
 (10,"Universe","Brow"),
 (11,"Jeff","Besos");

Here as we can see, we have set the stu_id as the primary key for our table student_details. It would ensure that the values in this column cannot be duplicated or NULL.

Moreover, we have set the data type as unsigned for the stu_id column. The above query creates a table with rows of the students’ first and last names.

To view the entries in the data, we use the following code.

SELECT * FROM student_details;

The above code would give the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller
8		  A				SAan
9		  Santa			Merry
10		  Universe		Brow
11		  Jeff			Besos

Now, let us create another table named student_details_dummy such that this new table is a subset of the original table. We can do this using the following query.

-- create the table student_details_dummy
CREATE TABLE student_details_dummy(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details_dummy
INSERT INTO student_details_dummy(stu_id,stu_firstName,stu_lastName)
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

Now, with the help of the query, we have created a new table named student_details_dummy and have added a few rows within it.

It is important to note that we have a limited number of students in this new table (7) compared to the student_details table, which has 11 students.

We can visualize the student_details_dummy table with the help of the following query.

select * from student_details_dummy;

The output of the query can be illustrated as follows.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Since we have the tables set up, let us use the not exists condition. Particularly, let us get data of only those students in the student_details table but not in the student_details_dummy table.

We can use the NOT EXISTS clause to get this done.

We can perform this operation by filtering data with the help of the following syntax.

SELECT * from table_name
WHERE NOT EXISTS (Another query);

The query loops through our entire table and finds values that do not match the values fetched from the second query. We can use the query below to get this done with our tables student_details and student_details_dummy.

SELECT stu_id as diff_students from student_details
WHERE NOT EXISTS (SELECT NULL from student_details_dummy where student_details.stu_id = student_details_dummy.stu_id);

Here, in the query, we fetch the stu_id from the student_details table such that there exist no common students between both of these tables.

The output of the query can be illustrated as follows.

diff_students
8
9
10
11

As we can see in the query, all the stu_id’s that are not present in the student_details_dummy table but are present in the student_details table are displayed here.

We have the alias diff_students with the AS keyword in MySQL to increase the program’s readability.

Therefore, with the help of the queries, we can efficiently get the names of all the students from a table based on a filtering condition using not exists efficiently and quickly in MySQL.