SQL ISNULL Vs NULLIF Function

The distinction between ISNULL and NULLIF functions will be discussed in this post.


SQL ISNULL Function

Replaces NULL with the replacement value supplied. If expression is not null, it returns its value.

ISNULL is a function that will return the second value if the first value is null, and the first value only if the first value is not null.

Example 1: Having NULL value in expression

Sql-server

SELECT ISNULL(NULL , 890);

Mysql

SELECT ISNULL(NULL);

Instead of null replacement value will be executed in sql database. In mysql value having null it return 1

Output: The output of above two query is

800
1

Example 2:Does not contain null value

Sql-server

SELECT ISNULL('DATABASE','EASY');

Mysql

SELECT ISNULL('DATABASE');

In above two query value doesn't have any null . The sql server return first value 'DATABASE' Then Mysql database return '0'

Output:

DATABASE
0

SQL NULLIF Function

If both the expressions are equivalent, it returns null. If both expressions are not equal, NULLIF returns the first one.

Example 1: In below example return NULLIF both expressions are the same..

SELECT NULLIF("DATABASE", "DATABASE");

The MySQL statement in the above function examines whether the first and second expressions are equal. It returns NULL IF both expressions are the same. Otherwise, the first expression will be returned.

Output: The output of Null if have same expression is

NULL

Example 2: In below example returns first expression ,IF both expressions are not same..

SELECT NULLIF("DATABASEE", "connection");

In above example we have 'DATABASE' and connection both are different expression so we get first value or expression.

Output: The output of above query is

DATABASE

SQL DIFFERENCE ISNULL And NULLIF FUNCTION

There are 2 main distinguish in isnull Vs nullif poits are tabulated:-

ISNULL NULLIF
If the first one is NULL, the second one is returned. If the two provided expressions are equal, returns NULL.
If the first expression is not NULL, it returns the first expression. If the supplied expressions are NOT equal, returns the first expression.