SQL ISNULL Vs NULLIF Function
The distinction between
NULLIF functions will be discussed in this post.
SQL ISNULL Function
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
SELECT ISNULL(NULL , 890);
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
Example 2:Does not contain null value
In above two query value doesn't have any null . The sql server return first value 'DATABASE' Then Mysql database return '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
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
SQL DIFFERENCE ISNULL And NULLIF FUNCTION
There are 2 main distinguish in isnull Vs nullif poits are tabulated:-
|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.|