MySQL EXISTS Operator

MySQL EXISTS operator is a boolean operator that returns true or false depending on the existence of any record in a subquery.

EXISTS operator returns true if the subquery returns one or more than one value.

EXISTS Operator in MySQL

EXISTS operator is used in MySQL to test for the existence of any record in a subquery.

EXISTS Operator works with a parent query and a child query. A parent query will execute if the child query returns any value.

Note: 
The EXISTS operator can be used with the NOT operator to negate its results, helping to determine if a given record is unique. 

Syntax

EXISTS syntax in MySQL is given below:

SELECT column_name01, column_name02……

FROM table_name

WHERE EXISTS (subquery);

MYSQL EXISTS Operator Examples

To understand how to use EXISTS Operator in MySQL, let’s look at some examples of EXISTS in MySQL. We will demonstrate a completely practical example from the first step to the end. Follow each step, to use MySQL EXISTS.

First, lets create a demo table on which we will perform the MySQL queries.

Demo MySQL Table

We create the first table “geeksforgeeks” in this example. To create this table and insert values, use the following SQL queries:

MySQL

CREATE TABLE geeksforgeeks(
  id varchar(100) PRIMARY KEY,
  name varchar(100),
  rank int
 );
INSERT INTO geeksforgeeks(id,name,rank)
VALUES('vish3001','Vishu',01);
INSERT INTO geeksforgeeks(id,name,rank)
VALUES('neeraj20','Neeraj',02);
INSERT INTO geeksforgeeks(id,name,rank)
VALUES('aayush15','Aayush',03);
INSERT INTO geeksforgeeks(id,name,rank)
VALUES('sumit25','Sumit',04);

SELECT * from geeksforgeeks;

Output:

Table – geeksforgeeks

To create the second table “Courses”, write the following SQL queries:

MySQL

CREATE TABLE courses(
  id varchar(100),
  course_name varchar(100),
  duration int,
 );

INSERT INTO courses(id,course_name,duration)
VALUES('vish3001','Python',30);
INSERT INTO courses(id,course_name,duration)
VALUES('sumit25','Python',30);
INSERT INTO courses(id,course_name,duration)
VALUES('vish3001','Java',40);
INSERT INTO courses(id,course_name,duration)
VALUES('aayush15','Python',30);
INSERT INTO courses(id,course_name,duration)
VALUES('neeraj20','Java',50);

SELECT * FROM courses;

Output:

Table – Courses

Now we have done with creating table, lets move to our example.

Example 1: EXISTS Operator With Simple Subquery

In this example, we are going to display all the id, name of geeksforgeeks table if and only if we have the same id’s in our courses table

Query

SELECT id,name
FROM geeksforgeeks 
WHERE EXISTS (SELECT courses.id FROM courses 
    WHERE courses.id = geeksforgeeks.id);

Output:

Result – 01

We can clearly notice all the id and names of the geeksforgeeks table are displayed here.

Example 2: EXISTS Operator With a Complex Subquery

In this example we are going to display all the records of the geeksforgeeks table where there exists a record where the id of the geeeksforgeeks table is equal to id of the courses table and course duration should be 30.

Query

SELECT id,name
FROM geeksforgeeks 
WHERE EXISTS (SELECT courses.id FROM courses 
    WHERE courses.id = geeksforgeeks.id and courses.duration = 30 );

Output:

Result – 02

We can clearly see that all the id(s) , and names of geeksforgeeks table are displayed who has taken a course of duration 30 (i.e. Python).

Example 3: MySQL EXISTS With DELETE Statement

In this example , we are going to delete all those rows whose course duration is 50. We will use the DELETE statement with EXISTS operator in this example. Let’s implement this.

Query

DELETE FROM geeksforgeeks
WHERE EXISTS (SELECT courses.id FROM courses 
    WHERE courses.id = geeksforgeeks.id and courses.duration = 50 );
    
SELECT * from geeksforgeeks;

Output:

Result – Delete Example

In the above image, we can clearly see that the row with id “neeraj20” has been deleted from the table. From the course table, we can notice that column id = “neeraj20” is the only row to have duration =50. Therefore, it has been deleted from the table.

MySQL EXISTS Operator Vs. IN Operator

Although both IN operator and EXISTS operator seems to perform similar types of tasks, there is vast difference between their implementation and uses. Let’s see how they are different from each other.

Difference Between EXISTS Operator and IN Operator
IN Operator	EXISTS Operator
IN operator is used to search a data into the table that matches the data within the specified set or subquery	EXISTS operator checks for the existence of a subquery.
It is less efficient for large dataset as it continues to execute until it traverses the whole.	EXISTS operator are efficient for large dataset as it stops execution when a matching data is found
Comparisons between parent queries and subqueries happen.	Comparisons between parent queries and subqueries do not happen.
IN operator is used to decrease the number of “=” operator. We can club all those conditions in one set or subquery.	Exists operator is used to check for existence of a subquery with more complex conditions than the IN operator conditions.
It checks for the existence of specific value from a finite set or small set.	It checks for the existence of a row matching specific complex conditions.

MySQL IN and EXISTS Operator Example

We are using the geeksforgeeks table to get all the rows with rank lies in the set (‘3’ , ‘2’, ‘1’).

Note: We are using the same tables from the Examples block.

IN operator example

Query

SELECT *
FROM geeksforgeeks 
WHERE rank IN ('3','2','1');

Output:

Result – In operator

In the above example, we can clearly observe all those rows with rank column belongs to the set (‘3′,’2′,’1’) are displayed in output.

EXISTS Operator Example

Let’s fetch all the values from the table geeksforgeeks where id column of geeksforgeeks matches with the id columns of the courses table.

Query

SELECT *
FROM geeksforgeeks 
WHERE EXISTS (SELECT courses.id FROM courses 
    WHERE courses.id = geeksforgeeks.id);

Output:

Result – Exists Operator

Important Points About MySQL EXISTS Operator

• Exists Operator works with a parent query and its corresponding subquery.
• Data of parent query is only displayed when subquery return any value.
• This operator return boolean values i.e. either TRUE or FALSE.
• Although it can show some resemblance with IN operator, these two operator has vast difference when it comes to implementation.
• EXISTS query is found efficient when dealing with large datasets.