MariaDB ISNULL + Examples - DatabaseFAQs.com

In this MariaDB tutorial, we will learn about the “MariaDB ISNULL” to deal with null values in columns or tables. Additionally, we will cover the following topics.

MariaDB ISNULL
MariaDB ISNULL ifnull
MariaDB ISNULL replace
MariaDB is null or empty
MariaDB case is null
MariaDB date is null
MariaDB not is null
MariaDB select isnull

Table of Contents

MariaDB ISNULL

The ISNULL is a function in MariaDB to evaluate the given expression is null or not. If the given expression is null, then it returns 1 otherwise for not null returns 0.

The syntax is given below.

ISNULL(expression);

Where,

ISNULL: It is a function that will evaluate whether a given expression is null or not. In the case of null expression, it returns 1 as result otherwise returns 0 as result for the not null expression.
expression: It is a value that is passed to the function ISNULL and checked for null or not null.

First, we will check with an example using the ISNULL function with the SELECT statement only.

SELECT ISNULL(2+2);

Here in the above code, we have provided the expression as 2+2 to function ISNULL. First, this expression is evaluated to 2+2 = 4, then the function will check this value 4 for null or not null, and this value is not null. So the function will output the result as 0 which indicates that the provided expression is not null.

The output of the above code is given below.

Take another example by passing the null value to ISNULL using the below code.

SELECT ISNULL(NULL);

In the above statement, we have passed the NULL value to function ISNULL, which returns the output as 1, This means provided value is null. The output of the code is given below.

Let’s taken one more example with tables Employee, the description of the table is given below.

Look at the above output in column emp_dept that contains the NULL values for employe Carls and Mary R. Justice, they belong to United Kindom and USA respectively.

Apply the ISNULL function on columns emp_dept of table Employee using the below code.

SELECT emp_name, ISNULL(emp_dept),emp_address FROM Employee;

The above query shows the records from columns emp_name, emp_dept and emp_address of table Employee using the SELECT statement.

Also, we have applied the ISNULL function on the column emp_dept using ISNULL(emp_dept) to check whether the column values are null or not.

The output of the code is given below.

The output shows the 0 for Non-Null values and 1 for Null values in column emp_dept for each employee.

Read: MariaDB Order By Clause

MariaDB ISNULL IFNULL

The IFNULL function also checks for null values in MariaDB but it gives more control over how we want to show the null values in the result set of the query. Where ISNULL only shows the 0 for null and 1 for non-null values, we already know about the ISNULL from the above sub-section.

With the help of IFNULL function, we can deal with null values while displaying the records.

The syntax of the IFNULL is given below.

IFNULL(expression_1,expression_2);

Where,

IFNULL: It is a function that returns the expression_1 when expression_1 is a non-null value otherwise returns the expression_2 if expression_1 is the null value.

Let’s take an example that we have used in the above sub-section.

SELECT emp_name, IFNULL(emp_dept,'Value not specified'),emp_address FROM Employee;

The above query shows the records from columns emp_name, emp_dept and emp_address of table Employee using the SELECT statement.

Also, look at the code, IFNULL(emp_dept,'Value not specified') this code will replace the null of columns emp_dept with the string Value not specified. The output of the above code is given below.

Look in the above output, how the null values are replaced with the string ‘Value not specified‘ using the function IFNULL.

Read: MariaDB Set Variable

MariaDB ISNULL Replace

The ISNULL function can only return 1 for null and 0 for non-null values, it cannot replace the null values with other values while showing the result. To replace the null value with other values in a result, we will use the function IF for that.

If you don’t know how the IF function works, please follow the tutorial “MariaDB IF tutorial”.

Here we will use the same table that we have specified in the above sub-section, Let’s replace the null values with other values using the below query.

SELECT emp_name, IF(ISNULL(emp_dept) =1,'Kitchen',emp_dept),emp_address FROM employee;

The output of the above code is given below.

Read: MariaDB regexp + Examples

MariaDB IS NULL or Empty

In MariaDB, the column can be checked whether it is null using operator IS NULL or empty using double single quote (‘ ‘).

Let’s take an example using the same table that we have used in the above sub-section.

SELECT emp_name, emp_dept,emp_address FROM employee
WHERE emp_dept IS NULL OR emp_dept = '';

The above code will show the records that have null values in columns emp_dept of the table Employee.

In this code SELECT emp_name, emp_dept,emp_address FROM employee, we show the records from columns emp_name, emp_dept, and emp_address from table Employee.

Then in WHERE clause filtering the records that contain null values or empty values in columns emp_dept using the statement emp_dept IS NULL OR emp_dept = ''. Where emp_dept IS NULL check for null value and emp_dept = ' ' checks for empty string or value in columns.

The output of the above code is given below.

From the output, we can see that two employee Carls and Mary R. Justice who belong to United Kindom and USA has null values in columns emp_dept.

Read: MariaDB Case Statement

MariaDB Case IS NULL

The CASE is an operator that is used in MariaDB queries like in the SELECT statement, here we will use the CASE within the SELECT statement to deal with null values within specific columns.

The syntax of CASE is given below.

CASE WHEN condition THEN result
ELSE result END

Where, If condition is evaluated to TRUE then the result part of THEN is executed, otherwise when condition becomes FALSE then the result part of ELSE is executed.

Let’s take an example with the CASE operator and deal with null values.

SELECT emp_name, 
CASE WHEN emp_dept IS NULL THEN 'N/A' ELSE emp_dept END,
emp_address FROM employee;

Here in the above code CASE WHEN emp_dept IS NULL THEN 'N/A' ELSE emp_dept END , this line contains the CASE comparison operator to compare the columns values with the given condition. Here we are comparing the columns emp_dept with NULL values.

If the columns contain the null values then the THEN 'N/A' part of the code replace the null values with string ‘N/A’, otherwise ELSE empt_dept is executed when a null value is not found.

The output of the above code is given below.

Look in the above code, how the null value is replaced with string ‘N/A’ in columns emp_dept of employees Carls and Mary R. Justice.

Read: MariaDB Window functions

MariaDB Date IS NULL

In MariaDB, IS NULL can be used to check the column of type date for null values.

Create a new table using the below query.

CREATE TABLE Node(id INT AUTO_INCREMENT PRIMARY KEY, start_date DATE DEFAULT NULL, stop_date DATE DEFAULT NULL);

Where,

CREATE TABLE: It is the command to create a new table here we are creating a new table as Node.
id INT AUTO_INCREMENR PRIMARY KEY: It is a column of type integer to store the unique identity of each node. The value of this column increases automatically as records will be inserted because it is defined as AUTO_INCREMENT and PRIMARY KEY column.
start_date date DEFAULT NULL: It is a column of type date to store the starting date of each node and by default, it can be null if records aren’t inserted for this column because define as DEFAULT NULL.
stop_date date DEFAULT NULL: It is a column of type date to store the stopping date of each node and by default, it can be null if records aren’t inserted for this column because define as DEFAULT NULL.

Insert the following records.

INSERT INTO Node(start_date,stop_date)VALUES('2021-03-12','2021-04-12');
INSERT INTO Node(start_date)VALUES('2021-05-23');
INSERT INTO Node(start_date,stop_date)VALUES('2021-06-15','2021-7-15'), ('2021-08-20','2021-09-20');
INSERT INTO Node(start_date)VALUES('2021-10-10');

View the table Node using the below query.

SELECT * FROM Node;

Let’s perform the IS NULL operator on the column stop_date to find the records that contain null values, from the output we can see that the columns stop_date contains null values.

SELECT * FROM Node
WHERE stop_date IS NULL;

Using the above code, we select records from each column that is represented using an asterisk (*) symbol within SELECT statements, Where the column stop_date contains the null values using the statement WHERE stop_date IS NULL.

The output of the above code is given below.

The output shows that nodes 6 and 9 haven’t any stop_date.

Read: MariaDB Row_Number Tutorial

MariaDB IS NOT NULL

In MariaDB, we can check the column for not null values using the IS NOT NULL operator. When our tables contain lots of null values and we want only the non-null values, to get these non-null values IS NOT NULL is used.

Let’s take an example by showing null values using the IS NOT NULL, as column emp_dept of the table, Employee contains null and non-null values.

SELECT emp_name, emp_dept, emp_address FROM employee
WHERE emp_dept IS NOT NULL ;

The above code shows the records from a table Employee where employee column emp_dept is not null or does not contain null values.

Look at the line in the above code WHERE emp_dept IS NOT NULL, this line filters the records only that has non-null values in the column emp_dept.

The output of the above code is given below.

Read: MariaDB DateTime Tutorial

MariaDB Select ISNULL

The ISNULL function can also be used with the SELECT statement to show whether a given expression is null or not.

The syntax is given below.

SELECT ISNULL(expression);

Where,

SELECT: It is a statement to select or show the specific column of a table or perform some operation with given data.

ISNULL: It is a function that will evaluate whether a given expression is null or not. In the case of null expression, it returns 1 as result otherwise returns 0 as result for the not null expression.
expression: It is a value that is passed to the function ISNULL and checked for null or not null.

Let’s check with an example using the below query.

SELECT ISNULL(1/0);

In MariaDB, when one is divided by zero then it returns null, In the above code, 1/0 means passing null values to function. So ISNULL outputs the result as 1 because the provided value is null.