Usage of IF EXISTS in MySQL Database

1. Basic Usage of the EXISTS Operator in MySQL
2. Using IF EXISTS Operator in MySQL

In this tutorial, we aim at exploring the IF EXISTS statement in MySQL.

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

-- 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");

The above query creates a table along with rows with student first name and last name in it. To view the entries in the data, we use the following code.

SELECT * FROM student_details;

The aforementioned code will 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

Basic Usage of the EXISTS Operator in MySQL

The EXISTS condition in MySQL is generally used along with a subquery that consists of a condition to be met. If this condition is met, then the subquery returns a minimum of one row. This method can be used to DELETE, SELECT, INSERT, or UPDATE a statement.

-- Here we select columns from the table based on a certain condition
SELECT column_name  
FROM table_name  
WHERE EXISTS (  
    SELECT column_name   
    FROM table_name   
    WHERE condition  
);  

Here, condition represents the filtering condition when selecting the rows from a particular column.

To check whether there exists a student in the stu_firstName column where the stu_id = 4, we will use the following code:

-- Here we save the output of the code as RESULT
SELECT EXISTS(SELECT * from student_details WHERE stu_id=4) as RESULT;  

The aforementioned code will give the following output:

RESULT
1

1 in the above code block represents a boolean value, which suggests that there is a student with stu_id = 4.

Using IF EXISTS Operator in MySQL

Sometimes, we wish to check the existence of a particular value in a table and alter our output based on the existence of that condition. The syntax for this operation is as follows:

SELECT IF( EXISTS(
             SELECT column_name
             FROM table_name
             WHERE condition), 1, 0)

Here, the output of the query is 1, if the IF statement returns True. Otherwise, it returns 0.

Let us write a query that returns Yes, exists, if a student with stu_id as 4 exists in the table. Otherwise, we want to return No, does not exist. To perform this operation, take a look at the code below:

SELECT IF( EXISTS(
             SELECT stu_firstName
             FROM student_details
             WHERE stu_id = 4), 'Yes, exists', 'No, does not exist') as RESULT;

The aforementioned code will give the following output:

RESULT
Yes, exists

Now, let’s try to find a student with stu_id = 11. This operation can be performed with the following query:

SELECT IF( EXISTS(
             SELECT stu_firstName
             FROM student_details
             WHERE stu_id = 11), 'Yes, exists', 'No, does not exist') as RESULT;

The aforementioned code will give the following output:

RESULT
No, does not exist

Thus, we have successfully implemented IF EXISTS in MySQL.