When MySQL IN() function returns NULL?

Following are the two cases when MySQL IN() function returns NULL as result −

Case-1 − When expression on  left side is NULL

 IN() function will return NULL if the expression on the left side is NULL. Following example will demonstrate it −

mysql> Select NULL IN (1,2,3,4,10);
+----------------------+
| NULL IN (1,2,3,4,10) |
+----------------------+
|       NULL           |
+----------------------+
1 row in set (0.00 sec)

Case-2 − When one of expression in the list is NULL and no match is found

IN() function will return NULL if it does not find any match and one of the expressions in the list is NULL. If there would be a match and one of the expressions in the list is NULL, it will return 1 as output. Following example will demonstrate it −

mysql> Select 10 IN (NULL,11,12);
+--------------------+
| 10 IN (NULL,11,12) |
+--------------------+
|      NULL          |
+--------------------+
1 row in set (0.00 sec)

mysql> Select 10 IN (NULL,11,12,10);
+-----------------------+
| 10 IN (NULL,11,12,10) |
+-----------------------+
|         1             |
+-----------------------+
1 row in set (0.00 sec)