How MySQL evaluates if we use EXISTS operator with a subquery that returns no rows?

If a subquery, used with EXIST operator, returns no rows, the expression EXIST returns FALSE and MySQL returns the empty set as output. It can be understood with the help of simple example using the following data from table ‘Customers’ −

mysql> Select * from Customers;
+-------------+----------+
| Customer_Id | Name     |
+-------------+----------+
|           1 | Rahul    |
|           2 | Yashpal  |
|           3 | Gaurav   |
|           4 | Virender |
+-------------+----------+
4 rows in set (0.00 sec)

mysql> Select * from Reservations;
+------+-------------+------------+
| ID   | Customer_id | Day        |
+------+-------------+------------+
|    1 |           1 | 2017-12-30 |
|    2 |           2 | 2017-12-28 |
|    3 |           2 | 2017-12-29 |
|    4 |           1 | 2017-12-25 |
|    5 |           3 | 2017-12-26 |
+------+-------------+------------+
5 rows in set (0.00 sec)

The MySQL query below is having the subquery with an EXIST operator that returns no rows. In this case, the EXIST expression returns FALSE hence the result set is an empty set.

mysql> Select Name from Customers WHERE EXISTS (SELECT * FROM Reservations WHERE customer_id = 4);
Empty set (0.00 sec)

Monica Mona

Published on 21-Feb-2018 13:33:19

• Related Questions & Answers
• How MySQL evaluates if we use EXISTS operator with the subquery that returns NULL?
• How MySQL SUM() function evaluates if it is used with SELECT statement that returns no matching rows?
• What MySQL returns if we use UNIX_TIMESTAMP() function with no argument?
• What happens if MySQL query returns no rows?
• How can we use a MySQL subquery with INSERT statement?
• How can we use a MySQL subquery with FROM clause?
• How can we create a MySQL view with a subquery?
• What MySQL returns if sub-query, used to assign new values in the SET clause of UPDATE statement, returns no rows?
• How can we nest a subquery within another subquery?
• How MySQL evaluates if I will use an expression within SUM() function?
• How can we use a subquery that contains a reference to a table that also appears in the outer query?
• MySQL query that returns a specific string if column is null?
• What MySQL ASCII() function returns if no parameter is provided to it?
• What MySQL CHAR_LENGTH() function returns if no parameter is provided to it?
• How can we filter data with the help of MySQL subquery?