MySQL ISNULL

Introduction to MySQL ISNULL

The following article provides an outline for MySQL ISNULL. ISNULL () function is used when you want a test if a value returned is NULL or not. If the expression value is NULL, then value returned is 1. If the expression value is NOT NULL, then 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 the 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:

Here the expression value is not NULL because of which 0 value is returned.

Code:

select isnull( NULL) AS "IS NULL";

Output:

Here the expression value is NULL because of which the value returned is 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 us select the table, and below output rows are display.

Output:

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

Now let us get the rows that have 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:

Here in the above expression value is not NULL because of which the value that is returned is 0.

Code:

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

Output:

Here in the above expression value is not NULL because of which the value that is 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:

Here in the above expression value is NULL because of which the 1 is returned.

Code:

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

Output:

Here in the above expression value is Negative, which is not NULL because of which the value is returned is 0.

Code:

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

Output:

Here in the above expression value is Negative, which is not NULL because of which the value is returned is 0.

Code:

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

Output:

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

This is a guide to MySQL ISNULL. Here we discuss the introduction; how does MySQL ISNULL works? Along with example respectively. You may also have a look at the following articles to learn more –

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