NULLIF expresion

The SQL method NULLIF takes two arguments and, if the two arguments are equal, returns NULL; otherwise, it returns the first argument.

The syntax for using NULLIF is as follows:

SELECT NULLIF(expression1, expression2)

The two expressions that need to be compared in this case are expression1 and expression2. Expression1 is returned if they are not equal; otherwise, NULL is returned. When a column or variable might possibly contain a null value, which could result in errors or unexpected outcomes in some SQL procedures, NULLIF is frequently used to handle the situation. It can make sure that a comparison or calculation always yields a valid value, even if one of the inputs is null, by using NULLIF.

Consider the following query:

SELECT NULLIF(15, 15);

This query will return NULL since the two arguments (15 and 15) are equal.

The following query will return 5:

SELECT NULLIF(5, 10);

Here, 5 is returned because the two arguments are not equal.

Cases of using NULLIF in SQL

1. Using NULLIF to prevent division by zero errors

Use the syntax as below:

SELECT dividend / NULLIF(divisor, 0)

Consider syntax as here:

SELECT 100 / NULLIF(0, 0);

In this example, NULLIF is used to check if the second argument is equal to 0. If it is, then NULL is returned, which prevents a division by zero error from occurring.

2. Using NULLIF to replace null values with a default value

Use the syntax as below:

SELECT NULLIF(column_name, NULL) AS new_column_name

FROM table_name;

In the above syntax, column_name is the name of the column that may contain null values and table_name is the name of the table where the column belongs to. The NULL value is compared with column_name and if the values match then NULLIF returns a null value. Otherwise, it returns column_name.

SELECT NULLIF(column_name, ' ') AS column_name FROM table_name;

In this example, NULLIF is used to compare the value of the column_name column to an empty string. If the two values are equal, then NULL is returned. This effectively replaces any null values in the column_name column with an empty string.

The NULLIF can be used to replace null values with a default value by using it in combination with the COALESCE function.

The COALESCE function returns the first non-null value in a list of expressions. So, if passing in a null value and a default value to COALESCE, it will return the default value if the original value is null.

Here is an example of using NULLIF and COALESCE to replace null values with a default value:

SELECT COALESCE(NULLIF(column_name, ' '), 'default_value') AS new_column_name

FROM table_name;

In the above syntax, default_value is the default value that you want to replace null values with. The COALESCE function returns the first non-null value from a list of values. So, if column_name is null, COALESCE returns default_value. Otherwise, it returns column_name.

In this example, the NULLIF function checks if the column_name is an empty string (' '), and returns NULL if it is. The COALESCE function then takes the result of NULLIF and checks if it is null. If it is null, it returns the default_value.

Note that the default value can be any expression as a string or a number.

3. Using NULLIF to compare two columns and return a default value

Use the syntax as below:

SELECT NULLIF(column_name1, column_name2) AS comparison_result FROM table_name;

In the above sytax, NULLIF is used to compare the values in column_name1 and column_name2. If the two values are equal, then NULL is returned. Otherwise, the value in column_name1 is returned. This allows to compare two columns and return a default value if they are equal.