SQL NVL

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

Example 1

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>