Why we cannot use comparison operator(=) for getting the rows with NULL from a table?

MySQLMySQLi Database

We cannot use = (comparison operator) because we know that NULL is not a value. If we want to get the rows with NULL from a table then we need to use IS NULL operator in MySQL query. Following example using the data from ‘employee’ table will exhibit it −

Example

mysql> Select * from Employee WHERE Salary IS NULL;

+----+-------+--------+
| ID | Name  | Salary |
+----+-------+--------+
| 7  | Aryan | NULL   |
| 8  | Vinay | NULL   |
+----+-------+--------+

2 rows in set (0.00 sec)

The query above use IS NULL operator and produces the output where salary column is having NULL.

mysql> Select * from employee where salary = NULL;
Empty set (0.00 sec)

The query above use = (Comparison operator) hence produces the empty set because with NULL is not a value.

raja
Published on 20-Feb-2018 12:46:49
Advertisements