SQL NULL Functions

Following are the defined NULL functions in SQL.

1. ISNULL():- ISNULL() function is used to replace NULL values.
2. IFNULL():- IFNULL() function is used to replace NULL value with another value.
3. COALESCE():- COALESCE() function will return null, if all the expressions evaluate to null.
4. NULLIF():- If both arguments are not equal then the first argument is returned otherwise NULL is returned.


NULL Functions Syntax :

The syntax of the NULL Functions is −

ISNULL() Syntax:-
--------------------
SELECT column(s), ISNULL(column_name, value_to_replace)
FROM table_name;
--------------------
--------------------
IFNULL() Syntax:-
-------------------
SELECT column(s), IFNULL(column_name, value_to_replace)
FROM table_name;
--------------------
--------------------
COALESCE() Syntax:-
--------------------
SELECT column(s), CAOLESCE(expression_1,....,expression_n)
FROM table_name;
--------------------
--------------------
NULLIF() Syntax:-
-------------------
SELECT column(s), NULLIF(expression1, expression2)
FROM table_name;

NULL Functions Example :

Consider the Customer table with the following records -

ISNULL() Examples:- The following code is an example that is used to sum of salary of all Customer, if Salary of any Customer is not available (or NULL value), use salary as 8000:-

lfcdb=# SELECT SUM(ISNULL(Salary, 8000) AS Salary
FROM Customer;

The result for the respective sql query is as follows −

IFNULL() Examples:- The following code is an example that is used to sum of salary of all Customer, if Salary of any Customer is not available (or NULL value), use salary as 8000.:-

lfcdb=# SELECT SUM(IFNULL(Salary, 8000) AS Salary
FROM Customer;

The result for the respective sql query is as follows −

COALESCE() Examples:- The following code is an example of COALESCE() function in SQL.

lfcdb=# SELECT Name, COALESCE(Salary, Salary2) AS Salary
FROM Customer;

The result for the respective sql query is as follows −

NULLIF() Examples:- The following code is an example of NULLIF() function in SQL.

lfcdb=# SELECT Name, NULLIF(Salary, Salary2) AS Salary
FROM Customer;

The result for the respective sql query is as follows −

---

Visit :

---

Discussion