MySQL ISNULL function

This function is used to check the null value in table data. It returns the result in a Boolean form. If table data is null, then the output becomes 1. If table data is not null, then the output becomes 0.

Syntax

The ISNULL function syntax shows below.

ISNULL (value);

MySQL ISNULL function with table syntax shows below.

Select * from table_name WHERE ISNULL (column name);

Prerequisite for the comparison function

• Create table in the MySQL system using the below statement.

mysql> CREATE TABLE `marks` (
  `roll_no` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `physics` INT NULL,
  `chemistry` INT NULL,
  `maths` INT NULL,
  `english` INT NULL,
  PRIMARY KEY (`roll_no`));

• Insert value in the table.
• The output command shows the table format and its information.

mysql> Select * from marks;

Use of the ISNULL function

The following are the uses of the ISNULL function:

• The ISNULL function compares the column data between null and not null values.
• The ISNULL function uses to find out not null values in the table columns.
• The ISNULL function returns the Boolean value (1 or 0) of the table columns.

Example of the "ISNULL" function

Example1: Execute the following query to know the working procedure of the "null" values and their output.

mysql> SELECT ISNULL(NULL);

OUTPUT

The image shows the "1" value as output. It means the ISNULL function contains a null value in the table.

Example2: The "ISNULL" function with not null column example shows below.

mysql> SELECT ISNULL(20);

OUTPUT

The image shows the "0" value as output. It means the ISNULL function contains not null value in the table.

Example3: The "ISNULL" function with table data example shows below.

mysql> select ISNULL(physics) AS subject  from marks;

OUTPUT

The image shows the Boolean result of the given column. The "null" value columns display one (1) value. The "not null" value columns show zero (0) value.

Example4: The "ISNULL" function with the WHERE condition example shows below.

Execute the following query to understand the "ISNULL" statement with the WHERE clause. Here the ISNULL function applies to the single column of the table.

mysql> select ISNULL(physics) AS subject from marks where no > 2;

OUTPUT

Executing the query will return the following output where we see Boolean values 0 and 1.

Example5: The "ISNULL" function with table data example shows below. Here the ISNULL function applies to the multiple columns of the table.

mysql> select ISNULL(physics), ISNULL(chemistry), ISNULL(maths), ISNULL(english) from marks ORDER BY physics ASC;

OUTPUT

Executing the below output Where we see the data is displayed in ascending order.

Example6: The "ISNULL" function with the "WHERE" clause example shows below. Here, the "WHERE" clause applies to the physics column. The ISNULL function applies to the single column of the table.

mysql> select * from marks where ISNULL(physics);

OUTPUT

The image shows null values physics column data. Here, you can see the last two rows of the table.