How to INSERT IF NOT EXISTS in MySQL

In this tutorial, we aim at understanding how to use the INSERT IF NOT EXISTS clause in a table in the MySQL database.

We often insert certain values in a particular column of a table without checking their existence in the table. However, sometimes, it is necessary to avoid duplicate values while inserting them in a particular table.

Consider the example of a car dealership. While inserting data into the cars table, we do not want the car id of a particular type of car to be inserted twice into our table.

MySQL provides us with the INSERT IF NOT EXISTS clause that helps us perform this operation efficiently. The basic syntax for INSERT IF NOT EXISTS is as follows.

INSERT INTO name_of_the_table (column_name)
SELECT * FROM (SELECT value_name) AS val
WHERE NOT EXISTS (<conditonal expression>);

In the name_of_the_table we insert the value_name in the column_name if the conditional expression is met.

But before we begin, let us create a dummy dataset.

-- create the table student_details
CREATE TABLE student_details(
  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
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");

INSERT IF NOT EXISTS in MySQL

Let us understand how to use the INSERT IF NOT EXISTS in MySQL by inserting a new student named Preet with the stu_id as 8.

However, we will only insert this value if the first names of the existing students in the table do not match with Preet. We can do this operation using the following query.

INSERT INTO student_details(stu_id,stu_firstName, stu_lastName)
SELECT * FROM (SELECT 8 as stu_id, 'Preet' AS customer_name, 'Shah' AS stu_lastName) AS new_value
WHERE NOT EXISTS (
    SELECT stu_firstName FROM student_details WHERE stu_firstName = 'Preet'
) LIMIT 1;

Now, check the student_details table with the following query.

SELECT * from student_information;

The aforementioned query will give us 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

As we can see, no new values have been added to the table because the first name Preet already exists in the student_details table.

Let us try to add another student with stu_id as 9 and stu_firstName as Dhruv and stu_lastName as Shah. We can perform this operation using the following query.

INSERT INTO student_details(stu_id,stu_firstName, stu_lastName)
SELECT * FROM (SELECT 9 as stu_id, 'Dhruv' AS customer_name, 'Shah' AS stu_lastName) AS new_value
WHERE NOT EXISTS (
 SELECT stu_firstName FROM student_details WHERE stu_firstName = 'Dhruv'
) LIMIT 1;

As seen in the above query, we add the student with stu_firstName as Dhruv if it does not already exist in the table.

The output of the aforementioned query is as follows.

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

A new entry, Dhruv Shah with stu_id as 9, has been created because there are no duplicates in the first name.

Thus, with the help of the INSERT IF NOT EXISTS statement, we can efficiently enter details in a table given that the value to be inserted meets the required condition in the NOT EXISTS clause in MySQL.