• Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

Computer Notes

Library
    • Computer Fundamental
    • Computer Memory
    • DBMS Tutorial
    • Operating System
    • Computer Networking
    • C Programming
    • C++ Programming
    • Java Programming
    • C# Programming
    • SQL Tutorial
    • Management Tutorial
    • Computer Graphics
    • Compiler Design
    • Style Sheet
    • JavaScript Tutorial
    • Html Tutorial
    • Wordpress Tutorial
    • Python Tutorial
    • PHP Tutorial
    • JSP Tutorial
    • AngularJS Tutorial
    • Data Structures
    • E Commerce Tutorial
    • Visual Basic
    • Structs2 Tutorial
    • Digital Electronics
    • Internet Terms
    • Servlet Tutorial
    • Software Engineering
    • Interviews Questions
    • Basic Terms
    • Troubleshooting
Menu

Header Right

Home » Sql » Functions » SQL NVL Function
Next →
← Prev

SQL NVL Function

By Dinesh Thakur

The NVL and NVL2 functions allow you to test an expression to see whether it is NULL. If an expression is NULL, you can return an alternate, non-NULL value, to use in its place. Since any of the expressions in a DECODE statement can be NULL, the NVL and NVL2 functions are actually specialized versions of DECODE. The following example uses NVL2 to produce the same results as the DECODE:

SELECT lname, NVL2(manager_emp_id, ‘WORKER BEE’, ‘HEAD HONCHO’) emp_type FROM employee;

LNAME          EMP_TYPE

SMITH            WORKER  BEE

ALLEN           WORKER  BEE

WARD            WORKER  BEE

JONES           WORKER  BEE

MARTIN          WORKER  BEE

BLAKE           WORKER  BEE

CLARK           WORKER  BEE

SCOTT           WORKER  BEE

KING               HEAD  HONCHO

TURNER        WORKER  BEE

ADAMS         WORKER  BEE

JAMES           WORKER  BEE

FORD              WORKER  BEE

MILLER          WORKER  BEE

NVL2 looks at the first expression, manager_emp_id in this case. If that expression evaluates to NULL, NVL2 returns the third expression. If the first expression is not NULL, NVL2 returns the second expression. Use NVL2 when you wish to specify alternate values to be returned for the case when an expression is NULL, and also for the case when an expression is not NULL.

The NVL function is most commonly used to substitute a default value when a column is NULL. Otherwise, the column value itself is returned. The next example shows the ID of each employee’s manager, but substitutes the word ‘NONE’ when no manager has been assigned (i.e., when manager_emp_id is NULL):

SELECT emp.lname employee, NVL(mgr.lname, ‘NONE’) manager FROM employee emp LEFT OUTER JOIN employee mgr ON emp.manager_emp_id = mgr.emp_id;

EMPLOYEE  MANAGER

FORD              JONES

SCOTT            JONES

JAMES            BLAKE

TURNER          BLAKE

MARTIN          BLAKE

WARD             BLAKE

ALLEN             BLAKE

MILLER           CLARK

ADAMS           SCOTT

CLARK            KING

BLAKE            KING

JONES            KING

SMITH            FORD

KING              NONE

Even though DECODE may be substituted for any NVL or NVL2 function, most people prefer to use NVL or NVL2 when checking to see if an expression is NULL, presumably because the intent is clearer. Hopefully, the next section will convince you to use CASE expressions whenever you are in need of if-then-else functionality. Then you won’t need to worry about which built-in function to use.

 

You’ll also like:

  1. SQL: DECODE Function
  2. SQL DATEPART() Function
  3. SQL GETDATE() Function
  4. SQL CAST() Function
  5. SQL: COUNT Function
Next →
← Prev
Like/Subscribe us for latest updates     

About Dinesh Thakur
Dinesh ThakurDinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps.

Dinesh Thakur is a Freelance Writer who helps different clients from all over the globe. Dinesh has written over 500+ blogs, 30+ eBooks, and 10000+ Posts for all types of clients.


For any type of query or something that you think is missing, please feel free to Contact us.


Primary Sidebar

SQL Tutorials

SQL Tutorials

  • SQL - Home
  • SQL - Select
  • SQL - Create
  • SQL - View
  • SQL - Sub Queries
  • SQL - Update
  • SQL - Delete
  • SQL - Order By
  • SQL - Select Distinct
  • SQL - Group By
  • SQL - Where Clause
  • SQL - Select Into
  • SQL - Insert Into
  • SQL - Sequence
  • SQL - Constraints
  • SQL - Alter
  • SQL - Date
  • SQL - Foreign Key
  • SQL - Like Operator
  • SQL - CHECK Constraint
  • SQL - Exists Operator
  • SQL - Drop Table
  • SQL - Alias Syntax
  • SQL - Primary Key
  • SQL - Not Null
  • SQL - Union Operator
  • SQL - Unique Constraint
  • SQL - Between Operator
  • SQL - Having Clause
  • SQL - Isnull() Function
  • SQL - IN Operator
  • SQL - Default Constraint
  • SQL - Minus Operator
  • SQL - Intersect Operator
  • SQL - Triggers
  • SQL - Cursors

Advanced SQL

  • SQL - Joins
  • SQL - Index
  • SQL - Self Join
  • SQL - Outer Join
  • SQL - Join Types
  • SQL - Cross Join
  • SQL - Left Outer Join
  • SQL - Right Join
  • SQL - Drop Index
  • SQL - Inner Join
  • SQL - Datediff() Function
  • SQL - NVL Function
  • SQL - Decode Function
  • SQL - Datepart() Function
  • SQL - Count Function
  • SQL - Getdate() Function
  • SQL - Cast() Function
  • SQL - Round() Function

Other Links

  • SQL - PDF Version

Footer

Basic Course

  • Computer Fundamental
  • Computer Networking
  • Operating System
  • Database System
  • Computer Graphics
  • Management System
  • Software Engineering
  • Digital Electronics
  • Electronic Commerce
  • Compiler Design
  • Troubleshooting

Programming

  • Java Programming
  • Structured Query (SQL)
  • C Programming
  • C++ Programming
  • Visual Basic
  • Data Structures
  • Struts 2
  • Java Servlet
  • C# Programming
  • Basic Terms
  • Interviews

World Wide Web

  • Internet
  • Java Script
  • HTML Language
  • Cascading Style Sheet
  • Java Server Pages
  • Wordpress
  • PHP
  • Python Tutorial
  • AngularJS
  • Troubleshooting

 About Us |  Contact Us |  FAQ

Dinesh Thakur is a Technology Columinist and founder of Computer Notes.

Copyright © 2023. All Rights Reserved.