MySQL NULL: ISNULL, IS NOT NULL, IFNULL, COALESCE

Null is a special keyword in MySQL. The Null is used for the “Unknown” value. It is not equal to either 0 or an empty string. Comparing Null to another value, even another NULL always yields NULL values.

There are several functions we use to check for NULL values.

MySQL 'NULL' in Table Creation

We can specify whether a table column can hold a NULL value or not using the NULL or NOT NULL constraints. By default, all columns are allowed to contain Null values. If we do not want a column to have a Null value, we can specify Not NULL constraint.

For example:

CREATE TABLE EMPLOYEES(
    EMP_ID INT AUTO_INCREMENT,
    FIRST_NAME VARCHAR(20) NOT NULL,
    LAST_NAME VARCHAR(20),
    BIRTH_DATE DATE DEFAULT '1900-00-00',
    HIRE_DATE DATE DEFAULT (CURRENT_DATE()),
    PRIMARY KEY(EMP_ID)
);

The First_name cannot be NULL in this table. If we try to insert a null value in the column, MySQL will raise an error.

insert into employees(emp_id,first_name,last_name,birth_date,hire_date)
values(24,NULL,'oak',default,default);

MySQL NULL in Insertion Using Insert Statement

If a column is not specified as “Not Null” we can insert NULL values in it explicitly. Also if a column allows NULL values, a default value is not specified for it, and we do not insert a value into it, then by default a NULL value is put in the column. For example the rating, salary columns.

insert into employees(emp_id,first_name,last_name,birth_date,hire_date)
values(default,'Samba', NULL ,default,default);

MySQL NULL in Update Statement

We can also explicitly set a value to NULL if the column allows it.

Example:

update employees
set  email = NULL
where emp_id = 24;

Example of error:

update employees
set  first_name = NULL
where emp_id = 24;

IS NULL in MySQL

We can use the IS NULL clause to filter all such records that have a null value in the table. Below example, the query returns the records when the value for email column is NULL.

select * from employees where email IS NULL;

IS NOT NULL in MySQL

We can get an exactly opposite result here when we use IS NOT NULL, it returns all non-null values. Below example, it returns the records from the table when the email column has values.

select * from employees where email IS NOT NULL;

IFNULL in MySQL

The IFNULL function checks if the column value is NULL or Not. IF it is NULL, then it replaces or returns the value that we mention in the second argument.

select first_name , IFNULL(rating,'NA') from employees;

Wherever the value of rating is NULL, the resultset will return a ‘NA’ value as we specified.

COALESCE in MySQL

The COALESCE function is sort of like an either-or. In COALESCE, we give three arguments. IF the First Argument is NULL, then MySQL checks the second argument and returns it if not null. If the second argument is also null, then the third value is returned.

select first_name ,rating, salary, COALESCE(rating,salary,'NA')
from employees;