MYSQL Null Function
Category : TECH Author : Ankur Rastogi Date : Sat Jun 04 2016 Views : 46

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 |
+----+-------------------+-----------------------+-----------------------+

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 |
+----+-------------------+-----------------------+-----------------------+
Disclaimer: The above content reflect author’s personal views and do not reflect the views of OYEWIKI. Neither OYEWIKI nor any person/organization acting on its behalf is liable to accept any legal liability/responsibility for any error/mislead in this information or any information available on the website. This website in no way accepts the responsibility for any loss, injury, damage, discomfort or inconvenience caused as a result of reliance on any information provided on this website.
If you want to add more comments to the article or you see any thing incorrect please write a comment below and we will surely get back to you.