SQL > SQL NULL > NVL Function

The NVL( ) function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.

Oracle's NVL(expression, replacement) replaces any NULL value with the specified replacement, leaving non-NULL values unchanged. Use NVL when writing Oracle SQL; use IFNULL for MySQL or ISNULL for SQL Server.

For example, if we have the following table,

Table Sales_Data

 Store_Name  Sales 
 Store A 300 
 Store B NULL 
 Store C 150 

The following SQL, 

SELECT SUM(NVL(Sales,100)) FROM Sales_Data;

would generate result below:

SUM(NVL(Sales,100))
550

This is because NULL has been replaced by 100 via the NVL function, hence the sum of the 3 rows is 300 + 100 + 150 = 550.

Frequently Asked Questions

Q: What does the SQL NVL function do?
A: NVL(expression, replacement) replaces a NULL value with the specified replacement and returns it. If the expression is not NULL, it returns the expression unchanged. It is Oracle-specific.

Q: Which databases support NVL?
A: NVL is available in Oracle. MySQL uses IFNULL(), SQL Server uses ISNULL(), and all major databases support COALESCE() for equivalent behavior.

Q: What is the difference between NVL and NVL2 in Oracle?
A: NVL(expr, replacement) returns the replacement only when expr is NULL. NVL2(expr, val_if_not_null, val_if_null) lets you specify different return values for both the NULL and non-NULL cases.

Q: How does NVL compare to COALESCE?
A: NVL accepts exactly two arguments. COALESCE accepts two or more and returns the first non-NULL value from the list. COALESCE is part of the SQL standard and works across all major databases.

Related Topics

Next: SQL COALESCE Function

This page was last updated on March 19, 2026.



Copyright © 2026   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact