MySQL ISNULL

Updated June 3, 2023

Introduction to MySQL ISNULL

The following article provides an outline for MySQL ISNULL. ISNULL () function is used when you want to test whether a value returned is NULL. If the expression value is NULL, then the value returned is 1. If the expression value is NOT NULL, the value returned is 0. We have only one argument in the ISNULL () function. It defines the expression in the syntax. In simple words, the ISNULL function accepts an expression as a parameter checks if the value is NULL or not and returns 1 or 0 based on the return value.

Syntax:

Below is the syntax for the ISNULL () function:

isnull (<expression>); /* - - - -  ISNULL () SYNTAX - - - */

Based on the expression return value, the “is null” function decides to return 1 or 0.

How Does MySQL ISNULL Works?

Now let us see the usage of the ISNULL () function in the SQL server.

Code:

select isnull ('Hello world! This is ISNULL function' ) AS "IS NULL";

Output:

In this case, the expression value is not NULL, resulting in the return of 0.

Code:

select isnull( NULL) AS "IS NULL";

Output:

In this case, the expression value is NULL, resulting in a returned value 1.

Now let us apply the ISNULL () function in a table.

Table creation:

Code:

CREATE TABLE test_NULL
(
SERIAL_NO INT
, NAME VARCHAR(20)
, LOCATION VARCHAR(20)
, AGE INT
, OCCUPATION VARCHAR(20)
, PHONE_NO VARCHAR(10)
);

Now let us insert data into the table:

Code:

INSERT INTO TEST_NULL VALUES (1, 'Rose', 'USA', 24, 'Software Engineer', '9876545676' );
INSERT INTO TEST_NULL VALUES (2, 'Rahul', 'India', NULL , 'Artist', '8765678432' );
INSERT INTO TEST_NULL VALUES (3, 'Will', 'Denmark', 24, 'Software Engineer', '7656789843' );
INSERT INTO TEST_NULL VALUES (4, 'Ben', 'Polland', NULL , 'Actress', '9123456435' );
INSERT INTO TEST_NULL VALUES (5, 'Sian', 'FIA', 24, 'Writer', '8453672456' );
INSERT INTO TEST_NULL VALUES (6, 'Rodger', 'Norway', 24, 'Software Engineer', '7893412564' );
INSERT INTO TEST_NULL VALUES (7, 'Harry', 'USA', NULL, 'Artist', '9237645128' );
INSERT INTO TEST_NULL VALUES (8, 'Kiyana', 'USA', 24, 'Software Engineer', '7453478562' );
INSERT INTO TEST_NULL VALUES (9, 'Pradhush', 'USA', 24, 'Writer', '7554637789' );
INSERT INTO TEST_NULL VALUES (10, 'Dawson', 'USA', NULL, 'Painter', '9996665558' );
Select * from TEST_NULL;

Let’s select the table and display the output rows shown below.

Output:

Here in the above output, you can see that the age of the people is NULL for a few rows.

Now let us get the rows with age values as NULL using ISNULL().

Code:

SELECT * FROM TEST_NULL where ISNULL(AGE) = 1;

Output:

Now we get the rows where the Age is null.

If you want to update the existing table where the value of AGE is NULL.

We can update using the below statement:

Code:

UPDATE TEST_NULL
SET AGE = 99 WHERE ISNULL (AGE)=1;
SELECT * FROM TEST_NULL;

Output:

Example of MySQL ISNULL

Now let us see the usage of the ISNULL () function in the SQL server.

Code:

Select isnull('234567')AS "IS NULL";

Output:

The value is not NULL in the given expression, resulting in a returned value of 0.

Code:

select isnull ('replace value of the NULL') AS "IS NULL";

Output:

Here in the above expression value is not NULL, so the value returned is 0.

Here in the above expression value is NULL, because of which the replacement values are returned.

Code:

select isnull( NULL) AS "IS NULL";

Output:

In the above expression, the value is NULL, which results in the return of 1.

Code:

select isnull ( -3455 ) AS "IS NULL";

Output:

In the above expression, the value is Negative, which is not NULL, as the returned value is 0.

Code:

select isnull( ' ') AS "IS NULL";

Output:

In the above expression, the value is Negative, which is not NULL, as the returned value is 0.

Code:

select isnull( ' ' ) AS "IS NULL";

Output:

In MySQL, ‘space’ is also considered a value. Because of this, if we perform ISNULL on ‘space’, it returns 0. As it doesn’t consider the value as NULL.

We hope that this EDUCBA information on “MySQL ISNULL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. MySQL WEEKDAY
2. MySQL DELETE Trigger
3. ALTER TABLE MySQL
4. MySQL NOT IN