How to Use The MySQL EXISTS Operator
In this tutorial, you will learn how to use the MySQL EXISTS operator. MySQL EXIST operator is often used in subqueries to test whether any record in a subquery exists.
If the subquery returns one or more records, the EXISTS operator returns true; otherwise, it returns false.
Learn more:
MySQL EXISTS Syntax
The syntax of the EXISTS operator is as follows:
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT *
FROM table_name
WHERE where_conditions);
Examples
As shown in the tables below, assume that the database contains tables named "customers" and "orders," which contain data for the demo.
Customers:
| customer_id | customer_code | customer_name | country |
|---|---|---|---|
| 1 | #CID_20010 | Josh Warner | Japan |
| 2 | #CID_20011 | Lisa Ramsey | South Korea |
| 3 | #CID_20012 | Clifton Christensen | Japan |
| 4 | #CID_20013 | Iris Wilkerson | United State |
| 5 | #CID_20014 | Charles Harmon | Japan |
| 6 | #CID_20015 | Glen Wood | United State |
| 7 | #CID_20016 | Francis Lewis | Germany |
| 8 | #CID_20017 | William Barnett | China |
Orders:
| order_id | customer_id | order_number | order_date | amount |
|---|---|---|---|---|
| 1 | 1 | #ORD_00010 | 2019-05-13 | 1980.00 |
| 2 | 2 | #ORD_00011 | 2019-05-15 | 1350.50 |
| 3 | 1 | #ORD_00012 | 2019-05-07 | 1230.40 |
| 4 | 1 | #ORD_00013 | 2019-05-23 | 1200.00 |
| 5 | 2 | #ORD_00014 | 2019-05-31 | 3450.00 |
| 6 | 3 | #ORD_00015 | 2019-06-02 | 3470.50 |
| 7 | 4 | #ORD_00016 | 2019-06-03 | 1230.80 |
| 8 | 5 | #ORD_00017 | 2019-06-06 | 1998.00 |
Example 1:
Let's take a look at the following SQL query:
SELECT * FROM customers c
WHERE EXISTS
(SELECT * FROM orders o);
There is no condition specified in the subquery, so the EXISTS operator returns true because there are records in the orders table.
MySQL will return all records in the "customers" table.
Example 2:
In this example, suppose you need to retrieve all customers who have placed at least one order.
The SQL query would look like this:
SELECT * FROM customers c
WHERE EXISTS
(SELECT * FROM orders o
WHERE c.customer_id = o.customer_id);
MySQL returns five records because five customers placed orders in the "orders" table.
Example 3:
Here is an example of setting multiple conditions for a subquery:
SELECT * FROM customers c
WHERE c.country = "Japan" AND EXISTS
(SELECT * FROM orders o
WHERE c.customer_id = o.customer_id AND o.order_date BETWEEN
"2019-05-05" AND "2019-05-16");
MySQL returns all customers in Japan that placed orders between 05/05/2019 and 05/16/2019. There is only one record that meets the specified conditions.
Summary
In this MySQL tutorial, you learned how to use the MySQL EXISTS operator. It is often used in subqueries and returns true if the subquery returns any records and false otherwise.