If Salary1 has value NULL and Salary2 has value NULL then wh

If Salary1 has value NULL and Salary2 has value NULL, then what will be
the value for these predicates?
– Sarlary1 = Salary2
– Salary1 <> Salary2
– Salary1 IS NULL
– Salary2 IS NOT NULL
– Salary1 IS NULL OR Salary2 IS NOT NULL

Solution

mysql> select * from user;
+----------+--------+---------+---------+
| fname    | lname | salary1 | salary2 |
+----------+--------+---------+---------+
| Anshu    | Anurag |   20000 |   20000 |
| Gopal    | Sharma |   40000 |    NULL |
| lalchand | Mali   |    NULL |   60000 |
| Sam      | Gupta |    NULL |    NULL |
| David    | Sharma |    NULL |    NULL |
+----------+--------+---------+---------+
5 rows in set (0.00 sec)

mysql> select * from user where salary1=NUll;
Empty set (0.00 sec)

mysql> select * from user where salary1!=NUll;
Empty set (0.01 sec)

mysql> select * from user where salary1<>salary2;
Empty set (0.00 sec)

NOTE: =,!= and <> do not works in mysql query.They always return empty set.

mysql> select * from user where salary1=salary2;
+-------+--------+---------+---------+
| fname | lname | salary1 | salary2 |
+-------+--------+---------+---------+
| Anshu | Anurag |   20000 |   20000 |
+-------+--------+---------+---------+
1 row in set (0.00 sec)

NOTE: Above query did not return rows which has salary1 and salary2 as NULL Values;

mysql> select * from user where salary1 IS NULL;
+----------+--------+---------+---------+
| fname    | lname | salary1 | salary2 |
+----------+--------+---------+---------+
| lalchand | Mali   |    NULL |   60000 |
| Sam      | Gupta |    NULL |    NULL |
| David    | Sharma |    NULL |    NULL |
+----------+--------+---------+---------+
3 rows in set (0.00 sec)

mysql> select * from user where salary2 IS NOT NULL;
+----------+--------+---------+---------+
| fname    | lname | salary1 | salary2 |
+----------+--------+---------+---------+
| Anshu    | Anurag |   20000 |   20000 |
| lalchand | Mali   |    NULL |   60000 |
+----------+--------+---------+---------+
2 rows in set (0.00 sec)

mysql> select * from user where salary1 IS NULL OR salary2 IS NOT NULL;
+----------+--------+---------+---------+
| fname    | lname | salary1 | salary2 |
+----------+--------+---------+---------+
| Anshu    | Anurag |   20000 |   20000 |
| lalchand | Mali   |    NULL |   60000 |
| Sam      | Gupta |    NULL |    NULL |
| David    | Sharma |    NULL |    NULL |
+----------+--------+---------+---------+
4 rows in set (0.00 sec)

NOTE: IS NULL,IS NOT NULL works for mysql query and returns result.

Thanks a lot