IFNULL COALESCEWE 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.
IFNULLWe can use IFNULL in MySQL database to decide how to handle the null data of a table. With this we can change the data displayed from class column
By the above query we can display data and change the NULL data to value not known for display purpose only.
Same way we can make mathematical calculation on it.
We will use in a division command like this
COALESCEWe can also use coalesce same way like IFNULL. Here is one example
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.
WE will get highest mark as 96, same way we can use minimum command like this.
WE will get 18 as minimum mark. Note how the NULL data are ignored.
Let us count the number of records .
WE will get 35 as output as there are 35 records in our student3 table. Here all the records are counted including records with NULL value. But now let us try to count records using mark column.
Here the output is 32. The COUNT command has ignored the NULL data and only considered records having valid or known data.
Download sql dump of student3 table