Open In App

MySQL EXISTS Operator

Last Updated : 23 Apr, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

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 1

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 2

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:

exists operator example

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:

exists_input01

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:

exists with delete

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:

InoperatorExists

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:

gfgexists-table

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.


Previous Article
Next Article

Similar Reads

MySQL IN vs. EXISTS
In MySQL, We have two commonly used clauses in SQL queries that are EXISTS and IN used for querying data efficiently for high-performance applications. EXISTS and IN are the two most important clauses that are used to filter and extract data from the database. Although they both use subqueries in the same way, they serve different purposes and are
4 min read
MySQL INTERSECT Operator
In relational databases, MySQL stands as a cornerstone for managing and manipulating data. Among its arsenal of SQL operators lies a potent tool: the INTERSECT operator. In this article, we'll delve into the syntax, usage, and applications of MySQL's INTERSECT operator, accompanied by practical examples to demonstrate its capabilities. MySQL INTERS
4 min read
Check if there exists a subsequence such that its cumulative AND is equal to X
Given an array A[] of length N along with an integer X. Then the task is to output the subsequence such that the cumulative AND of all elements that are present in the subsequence is equal to X. If such a subsequence is present, then print it else print -1. Examples: Input: N = 3, A[] = {67, 44, 23}, X = 7Output: -1Explanation: It can be verified t
7 min read
SQL Server EXISTS
The EXISTS operator is used when we are dependent on another subquery which can be in the same table or a different table. When the subquery returns any rows the EXISTS operators return true otherwise false. EXISTS Operator simply checks whether the subquery returns any row. It gives true or False based on the existence of rows. In this article, yo
4 min read
Check if there exists two non-intersect ranges
Given two non-negative integers X and Y, the task is to output the two non-intersecting ranges of non-negative integers such that the sum of the starting and ending point of the first range is equal to X and the product of the starting and ending point of the second range is equal Y. If there is no possible answer output -1. If there are multiple p
7 min read
How to Check if a Specific Element Exists in a Set in JavaScript ?
To check if a specific element exists in a Set in JavaScript, you can use the has method. The has method returns a boolean indicating whether an element with the specified value exists in the Set Syntax:myset.has(value);Parameters:value: It is the value of the element that has to be checked if it exists in the Set.Example 1: Here, we have checked w
1 min read
How to Update If Row Exists Else Insert in SQL Server
Data update and data insert are two important functions to add and update data in SQL Server Tables. Using SQL queries we can check for specific data if it exists in a table. The update query with the WHERE Clause can be used to update data passed from the front end or any other data generated from data processing during runtime. Sometimes it may b
6 min read
How to Insert If Not Exists in SQL SERVER?
Adding Data to a table in SQL Server is a key operation. Data can be inserted into tables using many different scenarios like plain data inserted into a table without checking anything or checking if data already exists in the target table and only if the data does not exist then the new data is inserted. There are many methods to check the data if
8 min read
Difference between EXISTS and IN in PL/SQL
PL/SQL is a procedural language designed to enable developers to combine the power of procedural language with Oracle SQL. Oracle develops and serves as one of the three key programming languages embedded in the Oracle database, alongside SQL and Java. PL/SQL includes procedural language elements such as conditions and loops and can handle exceptio
7 min read
NOT IN vs NOT EXISTS in PL/SQL
PL/SQL is a Procedural Language/Structured Query Language. It allows developers to create robust, modular, and reusable code for implementing data manipulation, and transaction control in databases. It is widely used for developing stored procedures, functions, triggers, and other database objects to enhance the functionality and performance of dat
4 min read