NULL value with IFNULL COALESCE count maximum and minimum SQL commands

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.

IFNULL

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.

id	name	ifnull(class,'not known')	mark
1	John Deo	Four
2	Max Ruin	not known	85
3	Arnold	Three
4	Krish Star	not known
5	John Mike	Four
6	Alex John	not known	55
7	My John Rob	5	5
8	Asruid	Five	85
9	Tes Qry	Six	78

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`

COALESCE

We can also use coalesce the same way like IFNULL. Here is one example, here if mark is NULL then it will return 1.

SELECT id, name, class, (100 / COALESCE(mark,1) ),mark FROM `student3`

Example:

SELECT COALESCE(first_name, last_name) AS full_name FROM customers

In this example, if first_name is NULL for a customer, COALESCE will return their last_name. If both first_name and last_name are NULL, it will return NULL.

The main difference between IFNULL and COALESCE is the number of parameters. IFNULL takes two and COALESCE can take a list of arguments and returns the first non-NULL value it encounters.

Here is our cutomer table with first, middle and last name columns.

id	first_name	middle_name	last_name
1		King
2			Queen
3	Jack
4
5	Arnold	K	St
6			Ravi

SQL dump to create customer table with data is available at the end of this page.

SELECT id,COALESCE(first_name,middle_name,last_name) as name
  FROM `customer`

Here from first name, middle name and last name the first non-NULL value is returned as name. If all are NULL then NULL is returned

id	name
1	King
2	Queen
3	Jack
4
5	Arnold
6	Ravi

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

CREATE TABLE `customer` (
  `id` int(3) NOT NULL,
  `first_name` varchar(10) DEFAULT NULL,
  `middle_name` varchar(10) DEFAULT NULL,
  `last_name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `customer`
--

INSERT INTO `customer` (`id`, `first_name`, `middle_name`, `last_name`) VALUES
(1, NULL, 'King', NULL),
(2, NULL, NULL, 'Queen'),
(3, 'Jack', NULL, NULL),
(4, NULL, NULL, NULL),
(5, 'Arnold', 'K', 'St'),
(6, NULL, NULL, 'Ravi');

Subscribe to our YouTube Channel here