EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL ISNULL
 

MySQL ISNULL

Updated June 3, 2023

MySQL ISNULL

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

MySQL ISNULL 1

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

Code:

select isnull( NULL) AS "IS NULL";

Output:

MySQL ISNULL 2

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:

MySQL ISNULL 3

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:

rows that have age values

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:

value of AGE is NULL

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:

MySQL ISNULL 6

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:

MySQL ISNULL 7

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:

MySQL ISNULL 8

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

Code:

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

Output:

MySQL ISNULL 9

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:

which the value is returned is 0

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:

value is returned is 0

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.

Recommended Articles

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW