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)
Published on 21-Feb-2018 18:01:49
