Geez, write a simple test case:
CREATE TABLE TryNull ( x INT NULL );
INSERT INTO TryNull (x) VALUES (NULL), (0), (1), (123);
SELECT x, x = NULL, x IS NULL, x = 1, x != 1, x <=> 1 FROM TryNull;
SELECT x FROM TryNull WHERE x = 1;
SELECT x FROM TryNull WHERE x != 1;
Output:
+------+----------+-----------+-------+--------+---------+
| x | x = NULL | x IS NULL | x = 1 | x != 1 | x <=> 1 |
+------+----------+-----------+-------+--------+---------+
| NULL | NULL | 1 | NULL | NULL | 0 |
| 0 | NULL | 0 | 0 | 1 | 0 |
| 1 | NULL | 0 | 1 | 0 | 1 |
| 123 | NULL | 0 | 0 | 1 | 0 |
+------+----------+-----------+-------+--------+---------+
mysql> SELECT x FROM TryNull WHERE x = 1;
+------+
| x |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> SELECT x FROM TryNull WHERE x != 1;
+------+
| x |
+------+
| 0 |
| 123 |
+------+
2 rows in set (0.01 sec)
Notice how neither of these picked up the row with NULL.
Yes there are subtle diffs with Perl. Tough.
Summary:
* NULL propagates through expressions (except for IS NULL, IS NOT NULL, and <=>). That is, a NULL operand to an expression cause the value of the expression to be NULL.
* Where a Boolean is needed (as in WHERE, HAVING, etc), 0 and NULL mean FALSE; anything else means TRUE.
The above discusses "Operators", not "Functions". Some Functions deliberately handle NULLs, some don't:
mysql> SELECT x, COALESCE(x, 44), IFNULL(x, 44), LEAST(x, 44) FROM TryNull;
+------+-----------------+---------------+--------------+
| x | COALESCE(x, 44) | IFNULL(x, 44) | LEAST(x, 44) |
+------+-----------------+---------------+--------------+
| NULL | 44 | 44 | NULL |
| 0 | 0 | 0 | 0 |
| 1 | 1 | 1 | 1 |
| 123 | 123 | 123 | 44 |
+------+-----------------+---------------+--------------+
The first argument to IF(,,) is a Boolean, so the previous rules apply:
mysql> SELECT x, IF(x, 'x is treated as True', 'FALSE') FROM TryNull;
+------+----------------------------------------+
| x | IF(x, 'x is treated as True', 'FALSE') |
+------+----------------------------------------+
| NULL | FALSE |
| 0 | FALSE |
| 1 | x is treated as True |
| 123 | x is treated as True |
+------+----------------------------------------+
An example of the propagation of NULL through an expression:
mysql> SELECT x, x+3, 10*(x+3) FROM TryNull;
+------+------+----------+
| x | x+3 | 10*(x+3) |
+------+------+----------+
| NULL | NULL | NULL |
| 0 | 3 | 30 |
| 1 | 4 | 40 |
| 123 | 126 | 1260 |
+------+------+----------+