NVL function in SQL

The NVL function in SQL is used to substitute a default value instead of a null value. The syntax is:

NVL(expr1, expr2)

Where expr1 is the value that might be null, and expr2 is the value to replace it with if it is null.

For example:

SELECT NVL(null, 0) FROM dual;

It would return 0 since the first argument is null.

The NVL function is available in Oracle Database and some other databases like PostgreSQL. In other databases, equivalent functions are:

• ISNULL() in SQL Server
• IFNULL() in MySQL
• COALESCE() in SQL Server, PostgreSQL, MySQL

So, while NVL is an Oracle-specific function, there are alternatives in other databases.

Some critical properties of NVL:

• It requires two arguments, a value and a default, to replace null.
• The two arguments must have compatible data types.
• It will return the same data type as the input arguments.
• If expr1 is not null, it will return expr1.

Typical use cases for NVL include:

- Substitute default values for null columns:

SELECT NVL(commission_pct, 0) FROM employees;

- Handle null values in expressions:

SELECT salary * NVL(commission_pct, 0) FROM employees;

- Coalesce column values:

SELECT NVL(COALESCE(col1, col2), 'N/A') FROM table;

- String concatenation with null handling:

SELECT first_name || ' ' || NVL(last_name, '') FROM contacts;

- Default values in DECODE:

SELECT 
  DECODE(grade, 'A', 'Excellent',
               'B', 'Good', 
               NVL(grade, 'No Grade'))
FROM students;

Compared to COALESCE(), NVL only takes two arguments instead of an unlimited number. So, it is less flexible but more straightforward to use for null replacement.

The NVL2 function takes three arguments and is also available in Oracle. It allows you to return a different result if the value is null vs not null:

NVL2(commission_pct, 'Has commission', 'No commission')

This makes substituting default values based on a null check in a single function call is easy.

In summary, NVL is a simple way to substitute a default value for nulls in Oracle Database. It avoids errors when handling null values in calculations and expressions. For portability, use COALESCE(), which is available across many databases. But NVL remains a handy tool for null handling in Oracle SQL.