- NVL function is specific to Oracle and is used to replace NULL values with a specified value. The syntax for the NVL function is NVL(column_name, replacement_value). For example, to replace NULL values in the "SALARY" column with 0, you would use the following query:
SELECT NVL(SALARY, 0) FROM EMPLOYEES;
- COALESCE function is a standard SQL function that Oracle also supports. It returns the first non-NULL value in a list of expressions. The syntax for the COALESCE function is: COALESCE(expression1, expression2, ..., expression_n). For example, to return the first non-NULL value in the "SALARY" and "COMMISSION" columns, you would use the following query:
SELECT COALESCE(SALARY, COMMISSION) FROM EMPLOYEES;
- NVL can only handle two arguments; it is used to replace a single column's NULL value with a specific value. On the other hand, COALESCE can handle multiple expressions, so it can be used to replace NULL values in multiple columns or with multiple values.
- NVL can only be used for a single column, whereas COALESCE can be used for multiple columns or expressions.
- NVL is slightly faster than COALESCE because it only has to check one value. COALESCE has to check all of the expressions passed to it to find the first non-NULL value.
If you need to replace a single column's NULL values with a specific value, you should use the NVL function. You should use the COALESCE function if you need to replace NULL values in multiple columns or with multiple values.
0 Komentar