SQL ISNULL Function

SQL > SQL NULL > ISNULL Function

The ISNULL( ) function is available in SQL Server, MySQL, SparkSQL, and HiveQL. However, their uses are different:

SQL Server

In SQL Server, the ISNULL( ) function is used to replace NULL value with another value.

For example, if we have the following table,

Table Sales_Data

Store_Name	Sales
Store A	300
Store B	NULL

The following SQL,

SELECT SUM (ISNULL(Sales,100)) FROM Sales_Data;

returns the following result:

SUM (ISNULL(Sales,100))

400

This is because NULL has been replaced by 100 via the ISNULL function, so the total becomes 300 + 100 = 400.

MySQL

In MySQL, the ISNULL( ) function is used to test whether an expression is NULL. If the expression is NULL, this function returns 1. Otherwise, this function returns 0.

For example,

ISNULL(3*3) returns 0

ISNULL(3/0) returns 1

SparkSQL, HiveQL

In SparkSQL and HiveQL, the ISNULL( ) function is used to test whether an expression is NULL. If the expression is NULL, this function returns true. Otherwise, this function returns false.

For example,

ISNULL(3*3) returns false

ISNULL(3/0) returns true

Next: SQL IFNULL Function

This page was last updated on October 11, 2024.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.