Mysql IFNULL
From w3cyberlearnings
Contents |
MySQL IFNULL Function
This operator is equal to NULL If/ELSE construct.
Syntax IFNULL
When expre1 is not null, return expre1; otherwise return expre2.
IFNULL(expr1,expr2);
Note
Zero is NOT equal to NULL.
Example 1
mysql> SELECT IFNULL(0,3); +-------------+ | IFNULL(0,3) | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) mysql> SELECT IFNULL(NULL,3); +----------------+ | IFNULL(NULL,3) | +----------------+ | 3 | +----------------+ 1 row in set (0.00 sec)
Example 2
mysql> SELECT * FROM myscore;
+----+-------+--------------+
| id | score | student_name |
+----+-------+--------------+
| 1 | 30 | Bob |
| 2 | 40 | Jing |
| 3 | 34 | Kim |
+----+-------+--------------+
3 rows in set (0.00 sec)
mysql> SELECT id,score,student_name,
-> IFNULL((score/(30-score)),'yes') as rate
-> FROM myscore;
+----+-------+--------------+---------+
| id | score | student_name | rate |
+----+-------+--------------+---------+
| 1 | 30 | Bob | yes |
| 2 | 40 | Jing | -4.0000 |
| 3 | 34 | Kim | -8.5000 |
+----+-------+--------------+---------+
3 rows in set (0.00 sec)
