NULL and NVL functions in SQL

Read this Oracle Tutorial Point article to know how to use the NULL and NVL Function and what are the conditions applicable on those functions in SQL in Oracle database.

NULL FUNCTION:
NULL is a value which is Unavailable, Unassigned, Unknown and Inapplicable. A NULL is not same as ZERO or blank space. If a row lacks the data for a particular column that value is said to be NULL or to contain NULL.

SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP;

If any column value is an Arithmetic expression is NULL, the overall result is also NULL. In this situation is termed as NULL propagation and has to be handled very carefully.

SQL> SELECT ENAME,JOB,SAL,COMM,15*SAL+COMM FROM EMP;

SQL> SELECT ENAME,JOB,SAL,COMM,5*SAL+COMM FROM EMP;

NVL FUNCTION:
The NVL function is used to convert a NULL value to an actual value.

Syntax:

         NVL (Exp1, Exp2)

• Exp1: is the source value or expression that may contain NULL.
• Exp2: is the target value for converting NULL.

NVL function can be convert any data type, the return value is always the same as the data type of Exp1. The data types of the source and destination must match.

• NVL(comm,0)
• NVL(Hiredate,’14-SEP-15’)
• NVL(job,’Not Assigned’)

Examples:

SQL> SELECT ENAME,SAL,COMM,SAL+NVL(COMM,0) FROM EMP;

SQL> SELECT ENAME,SAL,COMM,(SAL*12)+NVL(COMM,0) FROM EMP;

SQL> SELECT ENAME,SAL,COMM,(SAL+500)+NVL(COMM,0) FROM EMP;

Working with Aliases:
An Alias is the alternate name given for any Oracle object. Aliases in Oracle are two types,

• Column Alias
• Table Alias

The column alias renames a column heading. It is specified in the SELECT list by declaring the alias after the column name by using the space separator. Alias heading appear is UPPER casting by default. The alias should be declared in double quotes if it is against the specifications of Naming Convention.

The AS keyword can be used between the column name and alias. An alias effectively renames the SELECT list item for the duration of the query. An alias cannot be used anywhere in the SELECT list for operational purpose.

Examples:

SQL> SELECT ENO NUMBERS,
         ENAME NAME,
         SAL “BASIC SALARY”,
         JOB DESIGNATION 
     FROM EMP;

SQL> SELECT DNO AS “DEPARTMENT ID”,
  DNAME AS “DEPARTMENT NAME”
  LOC AS PLACE
     FROM DEPT;

SQL> SELECT HISAL AS “MAXIMUM SALARY”,
   LOSAL AS “MINIMUM SALARY”,
   GRADE
     FROM SALGRADE;