MYSQL Null Function

Various REDBMS provide MYSQL NULL Functions in different forms. NVL(), IFNULL(), COALESCE() All provide same functionality to treat NULL values as 0. 

MYSQL provides ISNULL() function to check whether the given column value passed in this is NULL or not. it gives outcome as 1 if value is NULL and 0 if it is something other than NULL.

Syntax for ISNULL() function is

SELECT column1_name, ISNULL(column2_name) FROM table_name;

IFNULL() and COALESCE() function are used to set value of any column value if find NULL. These function can come very handy when we have some operation to perform using these function values.

Syntax for IFNULL() and COALESCE() functiona are

SELECT column1_name, IFNULL(column2_name, default_value) FROM table_name;

SELECT column1_name, COALESCE(column2_name, default_value) FROM table_name;

Let us dig deeper with the help of an example

Consider table hostel_list which contains list of hostels.

+----+-------------------+-----------+----------+
| id | name              | occupancy | capacity |
+----+-------------------+-----------+----------+
|  1 | ASHOK BHAVAN      |         2 |     1090 |
|  2 | KAILASH BHAVAN    |         2 |      990 |
|  3 | RUDRAKSH BHAVAN   |         2 |      990 |
|  4 | KAVERI BHAVAN     |         1 |      690 |
|  5 | BHAGIRATHI BHAVAN |         4 |     2140 |
|  6 | NARMADA BHAVAN    |         6 |     3240 |
|  7 | GANGA BHAVAN      |         1 |      780 |
|  8 | YAMUNA BHAVAN     |         8 |     4580 |
|  9 | GANDHI BHAWAN     |      NULL |     NULL |
+----+-------------------+-----------+----------+

Now let us fetch the details of hostel which is undercosntruction or having ocuupancy as NULL. MYSQL Query for that will be

SELECT * FROM hostel_list WHERE ISNULL(occupancy);

+----+---------------+-----------+----------+
| id | name          | occupancy | capacity |
+----+---------------+-----------+----------+
|  9 | GANDHI BHAWAN |      NULL |     NULL |
+----+---------------+-----------+----------+

Now let us fetch the all the hostels and displaying capacity as 0 for underconstruction hostels.

+----+-------------------+---------------------+---------------------+
| id | name              | IFNULL(occupancy,0) | IFNULL(capacity, 0) |
+----+-------------------+---------------------+---------------------+
|  1 | ASHOK BHAVAN      |                   2 |                1090 |
|  2 | KAILASH BHAVAN    |                   2 |                 990 |
|  3 | RUDRAKSH BHAVAN   |                   2 |                 990 |
|  4 | KAVERI BHAVAN     |                   1 |                 690 |
|  5 | BHAGIRATHI BHAVAN |                   4 |                2140 |
|  6 | NARMADA BHAVAN    |                   6 |                3240 |
|  7 | GANGA BHAVAN      |                   1 |                 780 |
|  8 | YAMUNA BHAVAN     |                   8 |                4580 |
|  9 | GANDHI BHAWAN     |                   0 |                   0 |
+----+-------------------+---------------------+---------------------+

Simillarly we can also use COALESCE() for the same purpose, MYSQL Query for that will be

SELECT id, name, COALESCE(occupancy,0), COALESCE(capacity, 0) FROM hostel_list;

+----+-------------------+-----------------------+-----------------------+
| id | name              | COALESCE(occupancy,0) | COALESCE(capacity, 0) |
+----+-------------------+-----------------------+-----------------------+
|  1 | ASHOK BHAVAN      |                     2 |                  1090 |
|  2 | KAILASH BHAVAN    |                     2 |                   990 |
|  3 | RUDRAKSH BHAVAN   |                     2 |                   990 |
|  4 | KAVERI BHAVAN     |                     1 |                   690 |
|  5 | BHAGIRATHI BHAVAN |                     4 |                  2140 |
|  6 | NARMADA BHAVAN    |                     6 |                  3240 |
|  7 | GANGA BHAVAN      |                     1 |                   780 |
|  8 | YAMUNA BHAVAN     |                     8 |                  4580 |
|  9 | GANDHI BHAWAN     |                     0 |                     0 |
+----+-------------------+-----------------------+-----------------------+