MySQL EXISTS Operator with Examples


How to Use The MySQL EXISTS Operator

In this tutorial, you’ll learn how to use the MySQL EXISTS operator. The EXISTS operator is used in a subquery to test for the existence of any record in the subquery.

If the subquery returns one or more records, the EXISTS operator returns true, otherwise, returns false.

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

Suppose you have tables, named customers and orders, with the demo data in your database, as shown below.

Customers:

customer_idcustomer_codecustomer_namecountry
1#CID_20010Josh WarnerJapan
2#CID_20011Lisa RamseySouth Korea
3#CID_20012Clifton ChristensenJapan
4#CID_20013Iris WilkersonUnited State
5#CID_20014Charles HarmonJapan
6#CID_20015Glen WoodUnited State
7#CID_20016Francis LewisGermany
8#CID_20017William BarnettChina

Orders:

order_idcustomer_idorder_numberorder_dateamount
11#ORD_000102019-05-131980.00
22#ORD_000112019-05-151350.50
31#ORD_000122019-05-071230.40
41#ORD_000132019-05-231200.00
52#ORD_000142019-05-313450.00
63#ORD_000152019-06-023470.50
74#ORD_000162019-06-031230.80
85#ORD_000172019-06-061998.00

Example 1

Let’s take a look at the following SQL query:

SELECT * FROM customers c

WHERE EXISTS

    (SELECT * FROM orders o);

As you can see, there are no specified conditions in the subquery. So, the EXISTS operator returns true since the orders table has records. So, MySQL returns all the records of the customers table.

Example 2

In this example, let’s say you need to retrieve all customers who placed at least one order.
The SQL query would be as follows:

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

The following illustrates an example of multiple conditions in the 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 who are located in Japan and placed orders between 2019-05-05 and 2019-05-16. However, there is only one record that meets the specified conditions.

In this tutorial, you’ve learned how to use the MySQL EXISTS operator. The EXISTS operator is often used in a subquery and returns true if the subquery returns any record, otherwise, returns false.

Leave a Reply

Your email address will not be published. Required fields are marked *