Oracle SQL - Null Values in Expressions

  1. HOME
  2. Oracle SQL
  3. Select
  4. Column Expressions

Introduction

In case one or more variables in an expression evaluate to a null value, the result of the expression as a whole becomes unknown.

The following code shows the Effect of Null Values in Expressions.

The total yearly salary including commission for two out of four emp is unknown, because the commission column of those emp contains a null value.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w  w  w .j  a v  a 2s.c o m

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL>
SQL> select ename, msal, comm, 12*msal + comm
  2  from   emp
  3  where  empno < 7600;

ENAME    |      MSAL |      COMM | 12*MSAL+COMM
-------- | --------- | --------- | ------------
SMITH    |  01800.00 |  [N/A]    |  [N/A]
ALLEN    |  01600.00 |  00300.00 |     19500.00
WARD     |  01250.00 |  00500.00 |     15500.00
JACK     |  02975.00 |  [N/A]    |  [N/A]
BROWN    |  01250.00 |  01400.00 |     16400.00
BLAKE    |  02850.00 |  [N/A]    |  [N/A]
CLARK    |  02450.00 |  [N/A]    |  [N/A]
SCOTT    |  03000.00 |  [N/A]    |  [N/A]
KING     |  05000.00 |  [N/A]    |  [N/A]
BREAD    |  01500.00 |  00000.00 |     18000.00
ADAMS    |  01100.00 |  [N/A]    |  [N/A]

ENAME    |      MSAL |      COMM | 12*MSAL+COMM
-------- | --------- | --------- | ------------
JONES    |  08000.00 |  [N/A]    |  [N/A]

12 rows selected.

SQL>
SQL>

Related Topic