NULL value data managing by INSERT, UPDATE and Delete SQL commands

Null means missing or unknown value. Null is not equal to zero or blank space data in a column. Null is also not the same as an empty string. So to match a Null value we can use the IS NULL condition in our SQL statement to get the records from a database table. Here is an example.

NULL : In an exam some students got 0 mark and some have not appeared in the exam.
Here for the absent students, we will use NULL, as no data exist for these students and at the same time we can't use 0 marks for them.

For all our examples we have modified our student table by making class and mark column to accept null and changed the data to add some null values. You can download and install a copy of this table to test in your database.
SQL dump of student3 table

SELECT * FROM`student3` WHERE class IS NULL

This will display all the records which have NULL value for class column.

id	name	mark
2	Max Ruin	85
4	Krish Star
6	Alex John	55

Similarly we can display all the records which do not have null value for the class. Like this

SELECT * FROM `student3` WHERE class IS NOT NULL

Now we will get all the records without having NULL value in class column

Making the column data to null

We can make all the class data ( or column ) equal to NULL by updating the record like this

UPDATE `student3` SET class = null

We can't change the value of the column ( class here ) if the property is set to NOT NULL for the column. To make the column null for all the records we first have to make the table accept NULL value by updating the structure of the table.

ALTER TABLE `student3` CHANGE `class` `class` VARCHAR( 10 )
 CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT ''

After this change we can add NULL value to the record.

Deleting records with null value

We can delete records for which some column has null value. Here it is

DELETE FROM student3 WHERE class IS NULL

As we know null value means unknown data so how to use the value ? A situation may come where we have to multiply or apply any other mathematical calculation on all the data of a column ( say Mark in our student table ) which have some null data also. For this we have to use different sql commands like IFNULL, COALESCE, NVL. We will learn in next Part.
FNULL, COALESCE, NVL

NULL and average value

Presence or absence of NULL value can change the average value of a column ?
Read more on how NULL value changes average value of a column.

NULL value with GROUP BY

When using the SQL GROUP BY clause to group data, NULL values are treated distinctly and with a specific behavior.

NULL Values Are Grouped Together: If the column being grouped by contains NULL values, all NULL values are considered equal for the purposes of the GROUP BY clause and are grouped together into a single group. This means that you will get one group where the grouping column is NULL for all rows in that group.

SELECT class, COUNT(*) AS no  FROM `student3` GROUP BY class

class	no
Four	7
	3
Three	2
5	1
Five	2
Six	7
Seven	10
Nine	2
Eight	1

To display one group with the Null values as Not Known, here is the Query.

SELECT IFNULL(class,'Not Known') as class, COUNT(*) AS no  FROM `student3` GROUP BY class

class	no
Four	7
Not Known	3
Three	2
5	1
Five	2
Six	7
Seven	10
Nine	2
Eight	1

Null value with DISTINCT

NULL is considered distinct from all other non-NULL values. So, when we use DISTINCT query, the result set will include one row for each unique non-NULL value and one row for NULL (if NULL values are present in the column).

SELECT DISTINCT(class) FROM `student3`

For better readability we will show Not Known for null values by using IFNULL.

SELECT DISTINCT(IFNULL(class,'Not Known')) as class  FROM `student3`

class
Four
Not Known
Three
5
Five
Six
Seven
Nine
Eight

NULL is NOT equal to NULL

Try this query

SELECT NULL=NULL

Output is NULL as NULL is not equal to NULL
We can’t compare null value with null value as Null value meaning is absence of a value. So in our student3 table we have some null values in class column, but they will not be included in the result.

SELECT * from student3 WHERE class <> 'Six'

The above query output will not include the rows with class column having NULL value.

NULL safe operators

MySQL gives us null safe operator <=> to give us null values. This will return 1 if both operands are NULL. This query will return all records having NULL value in class column.

SELECT * FROM student3 WHERE class <=> NULL

We will get three records as output as we have three records with class column having NULL data.

One of our record ( id =4 ) has null value for both class and mark column and one more record has 5 as mark and class. But this query will return only one row. ( why ? )

SELECT * FROM student3 where class=mark

Output

id	name	class	mark
7	My John Rob	5	5

Though we have NULL value for class and mark columns but we can't compare NULL with NULL. So we will get one row as output ( not two )
By using NULL safe operator we can compare NULL values. This query will return two records as output.

SELECT * FROM student3 where class <=> mark

Output is here

id	name	class	mark
4	Krish Star
7	My John Rob	5	5

By using Null safe operators <=> we can include Null values in our comparison.
SQL dump of student3 table SELECT query

Subscribe to our YouTube Channel here

NULL Data in table