How to handle null value functions in Oracle (NVL, NVL2, NULLIF, etc.)

Preface

This article will introduce functions in Oracle that handle null values. Commonly used processing functions are: NVL(), NVL2(), NULLIF(), and COALESCE(). In addition, DECODE() and CASE() functions can also handle null values.

1. Function syntax

NVL function

The NVL function is a function used to handle null values ​​and is often used in database query statements. Its syntax is as follows:

NVL(expr1, expr2)

Among them, expr1 is the expression to be judged. If the value of the expression is empty, the value of expr2 is returned; if the value of expr1 is not empty, the value of expr1 is returned.

The main purpose of NVL functions is to process null values ​​in query results to prevent null values ​​from affecting subsequent calculations or processing. It can ensure the accuracy of query results and avoid errors or exceptions caused by null values. When processing query results in a database, NVL functions are often used to replace the default value for null values, or to perform conditional judgments and logical operations.

NVL2 function

The NVL2 function is a function in the Oracle database, and its syntax is as follows:

NVL2(expr1, expr2, expr3)

Where, expr1 is the expression to be detected, expr2 is the value returned when expr1 is not NULL, and expr3 is the value returned when expr1 is NULL.

The main purpose of the NVL2 function is to determine whether the expression expr1 is NULL. If it is not NULL, it returns expr2, otherwise it returns expr3. It can return different values ​​while judging the expression. Common application scenarios include:

• Handling NULL values: By using the NVL2 function, the NULL values ​​in the database can be replaced with other non-NULL values, thereby avoiding errors or failures in subsequent operations.

• Return different values: Return different values ​​according to different results of the expression. For example, different prompt information can be returned or different business logic can be executed according to the satisfaction of a certain condition.

• Stitching string: By using the NVL2 function, you can decide whether to splice the value of a field based on whether it is NULL.

It should be noted that the NVL2 function is suitable for Oracle databases, and there may be different implementation methods in other databases.

NULLIF function

The NULLIF function is a function in the Oracle database, and its syntax is as follows:

NULLIF(expr1, expr2)

Among them, expr1 and expr2 are the two expressions to be compared.

The main purpose of the NULLIF function is to compare the values ​​of two expressions. If the values ​​of the two expressions are equal, it returns NULL, otherwise it returns the value of expr1. Common application scenarios include:

• Handle the case of equal values: By using the NULLIF function, two expression values ​​can be processed and converted into NULL values. This can be easily judged or processed in subsequent operations.

• Avoid zero division error: In some cases, division operation may be divided by zero. Using the NULLIF function can return NULL when the denominator is zero to avoid zero division error.

• Control return value: By using the NULLIF function, the returned value can be controlled based on the result of the expression. For example, different results can be returned based on the satisfaction of a certain condition.

It should be noted that the NULLIF function is suitable for Oracle databases, and there may be different implementation methods in other databases.

COALESCE function

The COALESCE function is a function used to process NULL values. Its syntax is as follows:

COALESCE(value1, value2, …)
The parameters can be any number of values, COALESCE will return the first non-NULL value in order, and if all values ​​are NULL, then NULL.

The COALESCE function is often used in the following situations:

• Replace NULL values ​​with non-NULL values: When you need to process NULL values, you can use COALESCE to replace NULL values ​​with other non-NULL values.

• Select a non-NULL value: When there are multiple values ​​to choose from, you can use COALESCE to select the first non-NULL value.

DECODE function

The DECODE function is a conditional statement function that is supported in many databases. Its syntax is as follows:

DECODE(expression, search_value1, result1, search_value2, result2, …, default_result)

Its function is to return the first matching search value based on the result of the expression expression, and return the corresponding result. If there is no matching search value, the default result is returned.

DECODE functions are often used to perform data conversion or conditional judgment during query. It can map a value to different results according to different conditions, similar to a switch statement.

2. Differences in usage

From the above table, we can see that the function that handles null values ​​is the overall idea of ​​IF judgment. Return data based on the judgment result. You can use it as needed.

3. Test cases

Usage of DECODE function:

SELECT name, DECODE(sex, 'M', 'Male', 'F', 'Female', 'Unknown') AS gender FROM employees;

Focus on introducing the usage of COALESCE function:

• Returns the first non-null value in the two columns:

SELECT COALESCE(column1, column2) FROM table_name;

• Returns the first non-null value in both columns, and if both are empty, the default value is returned:

SELECT COALESCE(column1, column2, 'default_value') FROM table_name;

• Returns the first non-null value in multiple columns:

SELECT COALESCE(column1, column2, column3, column4) FROM table_name;

• Returns the non-null value of the expression:

SELECT COALESCE(expression, 'default_value') FROM table_name;

Summarize

In short, through the above functions, the problem of handling null values ​​in Oracle database can be easily solved.

This is the article about how to handle null functions in Oracle (NVL, NVL2, NULLIF, etc.). For more information about Oracle processing null functions, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!