Oracle SQL - Select Null Values

Introduction

If a column contains no value, we say that such a column contains a null value.

The term null value is an indicator of missing information. A null value is not a value.

By default, null values are displayed on your computer screen as "nothing,".

Compare the results of the two queries.

select empno, ename, comm 
from   emp 
where  comm > 400; 
  
select empno, ename, comm 
from   emp 
where  comm <= 400;

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from www  .  ja  v  a  2s .co 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> select empno, ename, comm
  2  from   emp
  3  where  comm > 400;

    EMPNO | ENAME    |      COMM
--------- | -------- | ---------
 07003.00 | WARD     |  00500.00
 07005.00 | BROWN    |  01400.00

SQL>
SQL> select empno, ename, comm
  2  from   emp
  3  where  comm <= 400;

    EMPNO | ENAME    |      COMM
--------- | -------- | ---------
 07002.00 | ALLEN    |  00300.00
 07010.00 | BREAD    |  00000.00

SQL>

The first query returns 2 emp, so you might expect to see the other 12 emp in the result of the second query.

The two query results actually are not complementary.

When Oracle evaluates a condition, there are three possible outcomes: the result can be TRUE, FALSE, or UNKNOWN.

Only those rows for which the condition evaluates to TRUE will appear in the result.

However, the EMPLOYEES table contains several rows for which both conditions evaluate to UNKNOWN.

Therefore, these rows will not appear in either result.

Oracle SQL - Select Null Values

Introduction

Demo

Related Topics