MySQL EXISTS Operator
Last Updated :
23 Apr, 2024
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.
Share your thoughts in the comments
Please Login to comment...