The SQL NULLIF function is available in many vendors. Let’s take a look at what NULLIF does and what you can use it for.
Purpose of the SQL NULLIF Function
The SQL NULLIF function lets you compare two values and determine if they match or not:
- Match? The function will return NULL.
- Don’t match? The function will return the first value.
Syntax and Parameters
The syntax of the SQL NULLIF function is:
NULLIF (expr1, expr2)The parameters of the NULLIF function are:
- expr1 (mandatory): This is the first value to use for the comparison. It is also returned if the values do not match.
- expr2 (mandatory): This is the second value to use for the comparison.
As mentioned above, this function returns either:
- NULL if expr1 and expr2 match
- expr1 if expr1 and expr2 don’t match
Some things to note for these parameters:
- Both expr1 and expr2 can be numeric data types, and the function will work.
- If the data types of expr1 and expr2 are not numeric, they need to be the same. If not, the function will show an error.
NULLIF as a CASE Statement
The SQL NULLIF function can be rewritten as a CASE statement:
CASE expr1 WHEN expr2 THEN NULL ELSE expr1 END;Or, it can be written this way:
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END;You can read this article for more information on CASE statements.
This function can also be expressed as an IF statement (using general syntax, not SQL).
if (expr1 = expr2) then
return null;
else
return expr1;
end if;
What’s the Difference Between NULLIF and ISNULL?
The NULLIF function checks two values and returns either the first value or NULL. The ISNULL function will replace a value with a different value if the first value is NULL.
ISNULL is available in SQL Server and MySQL, but not Oracle or Postgres.
In MySQL, ISNULL returns either 1 or 0. In SQL Server, ISNULL returns a value you specify.
What’s the Difference Between NULLIF and Coalesce?
The NULLIF function checks two values and returns either the first value or NULL. The COALESCE function checks a list of values and returns the first value that is not NULL.
What’s the Difference Between NULLIF and IFNULL?
The NULLIF function checks two values and returns either the first value or NULL. The IFNULL function will replace a value with a different value if the first value is NULL.
IFNULL is available in MySQL, but not Oracle, SQL Server, or Postgres.
In MySQL, IFNULL is equivalent to SQL Server’s ISNULL function.
Examples of the SQL NULLIF Function
Here are some examples of the NULLIF function.
Example 1
This example uses NULLIF on two matching number values.
SELECT NULLIF(23, 23);Result:
(null)
The result is NULL because both values match.
Example 2
This example uses NULLIF on two number values, but they don’t match.
SELECT NULLIF(23, 29);Result:
23
The result shows 23 because the inputs don’t match, so expr1 is returned.
Example 3
This example shows NULLIF with two matching values, but they are entered as different numeric data types.
SELECT NULLIF(23.000, 23);Result:
(null)
The result is NULL because both values match, even though they are different numeric types (decimal and integer).
Example 4
This example shows NULLIF on two text values that match.
SELECT NULLIF('Sunday', 'Sunday');Result:
(null)
The result is NULL because both expr1 and expr2 values match.
Example 5
This example shows the NULLIF function on two text values that don’t match.
SELECT NULLIF('Sunday', 'Saturday');Result:
Sunday
The result shows expr1 which was provided as ‘Sunday’.
Example 6
This example shows NULLIF with two different variable types which have the same value.
SELECT NULLIF('23.7', 23.7);Result (Oracle):
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
The result is an “inconsistent datatype” error, because even though they are the same value, the data types are different.
Result (MySQL, Postgres, SQL Server):
(null)
This shows null without any errors.
Example 7 – Different Data Types
This example shows the NULLIF function, again with two different variable types, and the values don’t match.
Oracle
SELECT NULLIF(SYSDATE, 'Someday');Result:
ORA-00932: inconsistent datatypes: expected DATE got CHAR
An error is shown here because the data types do not match.
SQL Server
SELECT NULLIF(GETDATE(), 'Someday');Result:
Conversion failed when converting date and/or time from character string.
An error is shown here because the data types do not match.
MySQL
SELECT NULLIF(NOW(), 'Someday');Result:
Error: ER_WRONG_VALUE: Incorrect DATETIME value: 'Someday'
An error is shown here because the data types do not match.
Postgres
SELECT NULLIF(NOW(), 'Someday');Result:
error: invalid input syntax for type timestamp with time zone: "Someday"
An error is shown here because the data types do not match.
Example 8
This example uses a NULL value for expr1.
SELECT NULLIF(NULL, 'Sunday');Result (Oracle):
ORA-00932: inconsistent datatypes: expected - got CHAR
An error is shown because you can’t specify a NULL value for the expr1 value. You’ll get similar errors for other database vendors.
Example 9
This example uses a NULL value for expr2.
SELECT NULLIF('Sunday', NULL);Result:
Sunday
The result is the value of expr1, which is “Sunday”. Expr2 can be NULL for this function.
Similar Functions
Some functions which are similar to the Oracle NULLIF function are:
- COALESCE – Looks at many input values and returns the first non-NULL value.
- NVL – Allows you to check a value or expression, and if it is NULL, return a different value.
- NVL2 – Allows you to check for a value or expression, and specify different values if the result is true or false.
- CASE – This statement allows the functionality of an IF THEN ELSE statement in SQL.
You can find a full list of Oracle functions here.