SQL NULL Functions

What are NULL Values in SQL?

We use null values as placeholders in the database when there is missing data or the relevant data is not available.
Null values are a versatile data type that can be used in any column of any data type, including text, integer, blob, and CLOB.
When cleansing data and conducting exploratory data analysis, null values come in helpful.
We can use null values to help us remove ambiguity from data.
Null values are also beneficial for keeping the datatype consistent across the column.

Consider the following scenario: If a user writes their date of birth in the mobile number column by accident, a misunderstanding may arise while making contact.
To avoid this, we perform a data check before to insertion and replace any data that is not of date datatype with a null value.

Why do we need NULL Functions?

To conduct operations on the null values stored in our database, null functions are required. On NULL values, we can conduct functions that explicitly recognise if a value is null or not.

With this ability to recognise null data, one can perform operations on them similar to the aggregate methods in SQL. The following are some of the functions:

Function	Description
ISNULL()	Allows us to substitute the desired value for NULL values.
ISFULL()	If the value is NULL, the first value is returned; otherwise, the second value is returned.
COALESCE()	Allows us to return the arguments' first non-null values.
NULLIF()	Allows the user to replace the NULL value with the desired value.

Let's discuss all the NULL function types in brief,

ISNULL()

In SQL Server and MySQL, the ISNULL function has diverse functions. The ISNULL() function in SQL Server is used to replace NULL values.

Syntax:

SELECT column(s)
FROM table_name
WHERE ISNULL(column_name); 

Example Table Person :

Name	Salary
Ajay	20,000
Rahul	NULL
Karan	18,960
Suman	20,900

Query :

Fetch the name of all person whose salary is not available in the table (not NULL).
Query:

SELECT Name
FROM Person
WHERE ISNULL(Salary)

Output:

Name
Rahul

IFNULL()

This function is only accessible in MySQL; SQL Server and Oracle do not have it. There are two arguments to this function. The function returns the first parameter if the first argument is not NULL. The second argument is returned if the first is not provided. This function is typically used to substitute a value for a NULL value.

Syntax:

SELECT column(s), IFNULL(column_name, value_to_replace)
FROM table_name;    

Example Table Person :

Name	Salary
Ajay	20,000
Rahul	NULL
Karan	18,960
Suman	20,900

Query :

Find the sum of salary of all Person, if Salary of any person is not available (or NULL value), use salary as 8,000.
Query:

SELECT SUM(IFNULL(Salary, 8000) AS Salary
FROM Person;   

Output:

Salary
67860

COALESCE()

In SQL, the COALESCE function returns the first non-NULL expression in the list of inputs. The COALESCE function will return null if all of the expressions evaluate to null.

Syntax:

SELECT column(s), CAOLESCE(expression_1,....,expression_n)
FROM table_name;  

Example Table Person :

Name	Salary1	Salary2
Ajay	20,000	10,000
Rahul	NULL	21,000
Karan	18,960	20,000
Suman	20,900	10,000

Query :

Fetch the name, salary of each person.
Query:

SELECT Name, COALESCE(salary1, salary2) AS Salary
FROM Person;   

Output:

Name	Salary
Ajay	10,000
Rahul	21,000
Karan	20,000
Suman	10,000

NULLIF()

The NULLIF function has two input parameters. NULL is returned if the two arguments are equal. If this is not the case, the first argument is returned.

Syntax:

SELECT column(s), NULLIF(expression1, expression2)
FROM table_name;

Example Table Person :

Store	A	B
Store1	20	100
Store2	NULL	211
Store3	18	NULL

Query:

SELECT Store, NULLIF(A, B)
FROM Sales;  

Output:

Store	NULLIF(A, B)
Store1	20
Store2	NULL
Store3	NULL

We've seen that the use of NULL values is fundamental to the database and is done to ensure the database's integrity. Following that, we learned about the several functions that can be used with NULL values.
Some of the most important functions that work with NULL values, such as IFNULL(), ISNULL(), COALESCE(), and NVL(), are also discussed.

With this article at Logicmojo, you must have the complete idea of SQL NULL Functions.