How MySQL evaluates if we use EXISTS operator with the subquery that returns NULL?

If a subquery, used with EXIST operator, returns NULL, the expression EXIST NULL returns TRUE and MySQL returns the result based on an outer query. 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)

The MySQL query below is having the subquery with EXIST operator that returns NULL. In this case, the expression EXIST NULL returns TRUE hence the result set is based upon the outer query.

mysql> SELECT Name from Customers Where EXISTS(Select NULL);
+----------+
| Name     |
+----------+
| Rahul    |
| Yashpal  |
| Gaurav   |
| Virender |
+----------+
4 rows in set (0.00 sec)

Chandu yadav

Published on 21-Feb-2018 13:31:49

• Related Questions & Answers
• How MySQL evaluates if we use EXISTS operator with a subquery that returns no rows?
• 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?
• How can we use a MySQL subquery with INSERT statement?
• How can we use a MySQL subquery with FROM clause?
• MySQL query that returns a specific string if column is null?
• What MySQL returns when we use DISTINCT clause with the column having multiple NULL values?
• How MySQL SUM() function evaluates if the column having NULL values too?
• What MySQL returns if the argument of QUOTE() function is NULL?
• How can we filter data with the help of MySQL subquery?
• How MySQL evaluates if we export the data to CSV file from a table which contains a NULL value(s)?
• What MySQL EXPORT_SET() function returns if any of the argument is NULL?
• What MySQL returns if the first argument of INTERVAL() function is NULL?
• What is the use of comparison operators with MySQL subquery?
• How can we create a MySQL view with a subquery?