In this tutorial, we will learn about the EXISTS operator in MySQL. It is a handy operator while writing complex nested queries. We will understand the use of the EXISTS operator, and then we will see multiple examples of it with the SELECT, INSERT and DELETE statements. This is going to be an exciting tutorial so stay tuned till the end!
Also read: MySQL Select Statement – Fetching Data from MySQL Databases
Introduction to EXISTS and NOT EXISTS Operators
The EXISTS operator is a boolean type operator that drives the result either true or false. It is often used to check if the subquery returns any row.
Following is the correct syntax to use the EXISTS operator.
query [NOT] EXISTS (subquery);Code language: SQL (Structured Query Language) (sql)For example,
SELECT * FROM customer c WHERE EXISTS(SELECT * FROM orders o WHERE c.id=o.id);Code language: SQL (Structured Query Language) (sql)If the subquery returns any row in the above example, the EXISTS operator returns true. Else it returns false.
On the contrary, when we use the NOT EXISTS operator in the above query, and if the subquery returns any row, the NOT EXISTS operator returns false.
It is somewhat similar in functionality to the IN operator, but both are different by working and performance.
The EXISTS operator is not limited to the only SELECT statement. MySQL allows you to use the EXISTS operator in many places. For example, you can use the IF EXISTS operator while creating a stored procedure to prevent the error if there is already a stored procedure with the same name that exists.
Examples of the MySQL EXISTS Operator
We will see four examples here to demonstrate the use of the EXISTS operator. We will use the EXISTS operator with SELECT, INSERT, and DELETE statements.
Nevertheless, first, we will create two tables- customers and orders that will be used throughout the tutorial.
CREATE TABLE customers(
id INT AUTO_INCREMENT PRIMARY KEY,
cust_id INT,
name VARCHAR(100),
occupation VARCHAR(100),
age INT
); Code language: SQL (Structured Query Language) (sql)Creating another table
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id int NOT NULL,
cust_id int,
prod_name varchar(45),
order_date date
); Code language: SQL (Structured Query Language) (sql)Let’s insert data into the tables now.
INSERT INTO customers(cust_id,name,occupation,age)VALUES
(101, 'Jenos', 'dentist', 32),
(102, 'Peter', 'Developer', 30),
(103, 'James', 'teacher', 28),
(104, 'May', 'Scientist', 45),
(105, 'Earen', 'freelancer', 26),
(106, 'Rock', 'Actor', 25);Code language: SQL (Structured Query Language) (sql)Inserting data into the second table
INSERT INTO orders(order_id, cust_id, prod_name, order_date)
VALUES (1, '101', 'Shampoo', '2021-01-10'),
(2, '103', 'Laptop', '2021-02-12'),
(3, '106', 'Body Spray', '2021-02-15'),
(4, '104', 'Shirtt', '2021-03-05'),
(5, '102', 'TV', '2021-03-20'); Code language: SQL (Structured Query Language) (sql)Now let’s display the data of both tables to check if the values are correctly inserted.
SELECT * FROM customers;
SELECT * FROM orders;Code language: SQL (Structured Query Language) (sql)
We are all set. Now, let’s head toward the examples.
EXISTS With the SELECT Statement
Here, we will use the EXISTS operator to find all the customers who placed at least one order.
SELECT * FROM customers c WHERE EXISTS
(SELECT * FROM orders o WHERE c.cust_id=o.cust_id);Code language: SQL (Structured Query Language) (sql)
Here, we have used the subquery to check if the cust_id from the customers table is present in the orders table. If the subquery returns any result, then the EXISTS operator returns true, and therefore we get the expected result.
EXISTS with the INSERT Statement
Suppose you want to add the details of the customer into the archive table who didn’t place any order. You can do this easily using the EXISTS statement.
For this, let’s create a new customersArchive table.
CREATE TABLE customersArchive LIKE customers;Code language: SQL (Structured Query Language) (sql)Now, we will write a query to find the customers who didn’t place any order and insert them into the newly created table.
INSERT INTO customersArchive(cust_id,name,occupation,age)
SELECT cust_id,name,occupation,age FROM customers c
WHERE NOT EXISTS(
SELECT * FROM orders o
WHERE c.cust_id=o.cust_id
);Code language: SQL (Structured Query Language) (sql)The query was executed successfully. Let’s now display the customersArchive table data to check if we get the expected result.
SELECT * FROM customersArchive; Code language: SQL (Structured Query Language) (sql)
As you can see, we have received the expected output.
EXISTS with the DELETE Statement
In the previous example, we have inserted the details of customers who didn’t place any order into the new table. Now, we will remove those records from the first table.
DELETE FROM customers c
WHERE NOT EXISTS(
SELECT * FROM orders o
WHERE c.cust_id=o.cust_id
);Code language: SQL (Structured Query Language) (sql)
As you can see, the record of id 5 is deleted from the table because it didn’t have any orders in the orders table.
Conclusion
In this tutorial, we learned about the EXISTS operator in MySQL. As stated earlier, the EXISTS operator is somewhat similar to the IN operator. However, both are slightly different in terms of performance and way of working. We will come up with a new tutorial where we will explain the difference between IN and EXISTS operators based on multiple factors. Till then, practice some examples on the EXISTS operator. See you in the next tutorial!
References
MySQL Official documentation on EXISTS and NOT EXISTS operator.