The SQL NVL function is used to replace NULL value with an alternate value in Oracle. It’s similar to MS SQL Server’s ISNULL Function and mySQL’s IFNULL Function.
SQL NVL Function Syntax
NVL(exp1, exp2)
If exp1 is null, the NVL function will return exp2, otherwise it will return exp1. Exp1 and exp2 should be same or compatible with the data type.
SQL NVL Function Example
Table: Employees
EmployeeId | FirstName | LastName | Department | Salary |
---|---|---|---|---|
203 | Mazojys | Fxoj | Finance | 78000 |
204 | Jozzh | Lnanyo | Finance | NULL |
205 | Syllauu | Dfaafk | Finance | 57000 |
206 | Gecrrcc | Srlkrt | Finance | 62000 |
SELECT EmployeeId, NVL(Salary, 50000) AS Salary FROM Employees;
The result will look like:
EmployeeId | Salary |
---|---|
203 | 78000 |
204 | 50000 |
205 | 57000 |
206 | 62000 |
Example 2
SELECT SUM(NVL(Salary, 50000)) AS TotalSalary FROM Employees;
The result will look like:
TotalSalary |
---|
247000 |