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 “w3wiki” in this example. To create this table and insert values, use the following SQL queries:

MySQL

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

SELECT * from w3wiki;

Output:

Table – w3wiki

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 w3wiki table if and only if we have the same id’s in our courses table

Query

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

Output:

Result – 01

We can clearly notice all the id and names of the w3wiki 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 w3wiki 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 w3wiki 
WHERE EXISTS (SELECT courses.id FROM courses 
    WHERE courses.id = w3wiki.id and courses.duration = 30 );

Output:

Result – 02

We can clearly see that all the id(s) , and names of w3wiki 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 w3wiki
WHERE EXISTS (SELECT courses.id FROM courses 
    WHERE courses.id = w3wiki.id and courses.duration = 50 );
    
SELECT * from w3wiki;

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.