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 -

ID Name Salary Salary2
1 Aarav 18000 20000
2 Aarushi NULL 10000


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:-

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


The result for the respective sql query is as follows −

Salary
26000



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.:-

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


The result for the respective sql query is as follows −

Salary
26000



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

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


The result for the respective sql query is as follows −

Name Salary
Aarav 20000
Aarushi 10000



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

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


The result for the respective sql query is as follows −

Name Salary
Aarav 18000
Aarushi NULL





Visit :


Discussion


* You must be logged in to add comment.