3 min read · Feb 20, 2023
NULLIF is a built-in function in SQL that compares two expressions and returns NULL if they are equal, and the first expression otherwise. It is typically used to prevent divide-by-zero errors in calculations or to return NULL instead of a default value. The syntax for NULLIF is as follows:
NULLIF(expression1, expression2)
Where expression1 and expression2 are the two expressions that are being compared. If expression1 and expression2 are equal, NULLIF will return NULL. If they are not equal, NULLIF will return expression1.
NULLIF is often used in combination with other functions to control the behavior of calculations or data retrieval. For example, it can be used with the ISNULL function to replace a default value with NULL if the value of an expression is equal to the default. Here is an example:
SELECT ISNULL(NULLIF(col1, 0), 'N/A') AS result FROM table_name;
In this example, if the value of col1 is equal to 0, the result of the NULLIF function will be NULL, and the ISNULL function will replace it with the string ‘N/A’.
NULLIF is also commonly used to prevent divide-by-zero errors in calculations. For example, the following query calculates the ratio of two columns, but returns NULL if the denominator is 0:
SELECT col1 / NULLIF(col2, 0) AS ratio FROM table_name;
In this example, if the value of col2 is 0, the result of the NULLIF function will be NULL, and the division operation will not be performed.
Another use case for NULLIF is when you want to return NULL if a particular value is present in a column. For example, the following query returns NULL if the value of col1 is ‘abc’:
SELECT NULLIF(col1, 'abc') AS result FROM table_name;
In this example, if the value of col1 is ‘abc’, the result of the NULLIF function will be NULL, and if it is any other value, the result will be the value of col1.
NULLIF can also be used in aggregate functions, such as SUM or AVG, to exclude certain values from calculations. For example, the following query calculates the average of col1, excluding NULL values:
SELECT AVG(NULLIF(col1, NULL)) AS avg_col1 FROM table_name;
In this example, if the value of col1 is NULL, the result of the NULLIF function will be NULL, and the value will not be included in the calculation of the average.
NULLIF is a standard SQL function and is therefore available in most relational database management systems (DBMS) that support the SQL language. This includes popular DBMS such as MySQL, Oracle, Microsoft SQL Server, PostgreSQL, and SQLite.
Differerence Between NULLIF or NVL in Oracle
Both NULLIF and NVL are used to handle NULL values in Oracle, but they serve different purposes.
NVL is a function in Oracle that returns a specified value if the expression is null, otherwise it returns the expression itself. It takes two arguments — the first argument is the expression to be evaluated, and the second argument is the value to be returned if the expression is null. Here is an example of using NVL in Oracle:
SELECT NVL(col1, 'N/A') AS result FROM table_name;
In this example, if the value of col1 is null, the result of the NVL function will be ‘N/A’. If col1 is not null, the result will be the value of col1.
NULLIF, on the other hand, is a function in Oracle that compares two expressions and returns NULL if they are equal, and the first expression otherwise. It takes two arguments — the first argument is the expression to be evaluated, and the second argument is the value to be compared with the first expression. Here is an example of using NULLIF in Oracle:
SELECT NULLIF(col1, 0) AS result FROM table_name;
In this example, if the value of col1 is equal to 0, the result of the NULLIF function will be NULL. If col1 is not equal to 0, the result will be the value of col1.
Basically, NVL is used to substitute a value for a null expression, while NULLIF is used to return NULL if two expressions are equal, and the first expression otherwise.
Conclusion
NULLIF is a powerful and versatile function in SQL that provides control over the behavior of calculations and data retrieval. It can be used to prevent divide-by-zero errors, replace default values with NULL, exclude specific values from calculations, and much more. When used correctly, NULLIF can simplify your SQL code and improve the accuracy of your data analysis.
Leave a Reply