ISNULL , NVL , NULLIF , NVL2 , Coalesce function in SQL

ISNULL, NVL, NVL2, NULLIF, Coalesce function Used in SQL

Null value is unknown, undefined value in RDBMS databases. Lets see how we handle NULL value in our database. Different vendor provide different way to handle the NULL value by providing different function.

Lets see the different vendor RDBMS database handle NULL value functions:

SQL Server:
ISNULL function check the first expression if it find null value then it will replace it the secondary value defined in function.

ISNULL ( column/expression , replace_value )

Example:
SELECT ISNULL(Salery ,0) FROM HR.Employee;

NULLIF returns the first expression if the two expressions are not equal Otherwise NULLIF returns a null value.

NULLIF (exp1 , exp2 )

Example:
select nullif(column_value1, column_value2) from employee;

ORACLE:
NVL function check the first expression if it find null value then it will replace it the secondary value defined in function.

NVL (column/expression, Replace_value)

Example:
Select NVL(Salary, 0 ) from HR.employee;

NULLIF returns the first expression if the two expressions are not equal Otherwise NULLIF returns a null value.

NULLIF (exp1 , exp2 )

Example:
select nullif(column_value1, column_value2) from employee;

NVL2 function check first column value if its not null then give 2nd value as return otherwise 3rd value.

NVL2(column/exp, Value if not null, value if null)

Example:
select NVL2( city, 'present', 'n/a') from employee;

COALESCE : Function returns the value of the first of its input parameters that is not NULL.

COALESCE(value1, value2, value3)
Example:
select coalesce (address1 , address2 ) from employees;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.