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.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact