IFNULL() tests its first argument and returns it if it's not NULL, or returns its second argument otherwise : IFNULL « Function « SQL / MySQL






  1. SQL / MySQL
  2. Function
  3. IFNULL

IFNULL() tests its first argument and returns it if it's not NULL, or returns its second argument otherwise

     
mysql>
mysql> CREATE TABLE mytable
    -> (
    ->  name    CHAR(20),
    ->  id              CHAR(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mytable (name,id) VALUES ('Tom','198-48');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (name,id) VALUES ('Jack',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (name,id) VALUES ('Mary',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (name,id) VALUES ('Jane','475-83');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM mytable;
+------+--------+
| name | id     |
+------+--------+
| Tom  | 198-48 |
| Jack | NULL   |
| Mary | NULL   |
| Jane | 475-83 |
+------+--------+
4 rows in set (0.00 sec)

mysql>
mysql> SELECT name, IFNULL(id,'Unknown') AS 'id' FROM mytable;
+------+---------+
| name | id      |
+------+---------+
| Tom  | 198-48  |
| Jack | Unknown |
| Mary | Unknown |
| Jane | 475-83  |
+------+---------+
4 rows in set (0.00 sec)

mysql>
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.The IFNULL() function returns a value based on whether a specified expression evaluates to NULL.
2.IF( ) and IFNULL( ) are useful for catching divide-by-zero operations
3.Use IFNULL( ) to map their results appropriately:
4.IFNULL() takes two arguments. If the first argument is not NULL, that argument is returned; otherwise, the fun
5.SELECT ISNULL(NULL), ISNULL(0), ISNULL(1);
6.The following expression returns expr2 if expr1 is NULL, and expr1 otherwise: