WE have seen how NULL value is added removed or deleted from records of a table in part 1. Now we will read how to handle NULL data along wither other data for calculations and display. We will start with IFNULL function which will tell how to change the NULL value along with other data.


We can use IFNULL in the MySQL database to decide how to handle the null data of a table. With this we can change the data displayed from the class column
SELECT id, name, ifnull(class,'not known'), mark FROM `student3`
By the above query, we can display data and change the NULL data to value not known for display purpose only.
idnameifnull(class,'not known')mark
1John DeoFour
2Max Ruinnot known85
4Krish Starnot known
5John MikeFour
6Alex Johnnot known55
7My John Rob55
9Tes QrySix78

The same way we can make mathematical calculations on it.
SELECT id, name, class, (ifnull(mark,1)*2) FROM `student3`
We will use in a division command like this
SELECT id, name, class, (100 / ifnull(mark,1) ),mark FROM `student3`


We can also use coalesce the same way like IFNULL. Here is one example
SELECT id, name, class, (100 / COALESCE(mark,1) ),mark FROM `student3`

Effect of NULL data on count, average, max, min functions.

What happens if we try getting maximum or minimum mark when some records have null data. Let us try with this command.
SELECT max(mark) FROM `student3`
WE will get the highest mark as 96, the same way we can use minimum command like this.
SELECT min(mark) FROM `student3`
WE will get 18 as a minimum mark. Note how the NULL data are ignored.
Let us count the number of records.
select count(*) from student3
WE will get 35 as output as there are 35 records in our student3 table. Here all the records are counted including records with a NULL value. But now let us try to count records using the mark column.
select count(mark) from student3
Here the output is 31. The COUNT command has ignored the NULL data and only considered records having valid or known data.
SQL dump of student3 table NULL value


* indicates required
Subscribe to plus2net


    Post your comments , suggestion , error , requirements etc here

    SQL Video Tutorials

    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer