Oracle SQL - NULLs with EXISTS and IN

Introduction

NULL is not data, but rather a condition of data being unknown.

Null = Null, NULL != NULL or NULL IN (NULL) always evaluates to UNKNOWN, which is neither TRUE nor FALSE.

It is not possible to join two rows with NULLs in the join column.

The following code shows the trouble NULLs cause with EXISTS and IN queries.

Selecting All Managers Using IN or EXISTS

select ename
from emp
where empno in (select mgr from emp);

select e1.ename
from emp e1
where exists (select e2.mgr
                 from   emp e2
                 where e1.empno = e2.mgr);

EXISTS means "Is this value present in the specified table column?"

If that value is present, the answer is yes and the EXISTS expression evaluates to TRUE.

As NULLs cannot be equated, joining a NULL MGR to a NULL EMPNO does not return TRUE.

If the main query value does not have a match in the subquery, then the EXISTS evaluates to FALSE.

IN means "Does the value exist anywhere in this list?"

If one list value matches the external value, then the expression evaluates to TRUE.

One way to think of an IN list expression is to rephrase it as a series of OR expressions.

For example, the following

1234 IN (1234, NULL)

is equivalent to

1234 = 1234 OR 1234 = NULL

Demo

SQL>
SQL>-- from   www. j av  a  2  s  . c om
SQL> drop table emp;

Table dropped.

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 ename
  2  from emp
  3  where empno in (select mgr from emp);

ENAME
--------
JACK
BLAKE
CLARK
SCOTT
KING
FORD

6 rows selected.

SQL>
SQL> select e1.ename
  2  from emp e1
  3  where exists (select e2.mgr
  4                   from   emp e2
  5                   where e1.empno = e2.mgr);

ENAME
--------
JACK
BLAKE
CLARK
SCOTT
KING
FORD

6 rows selected.

SQL>

Related Topic