Trending October 2023 # How Does Mysql Isnull Works With Examples # Suggested November 2023 # Top 19 Popular

You are reading the article How Does Mysql Isnull Works With Examples updated in October 2023 on the website Nhunghuounewzealand.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How Does Mysql Isnull Works With Examples

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

Below is the syntax for the ISNULL () function:

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.

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.

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";

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.