Oracle SQL - NULLS with NOT EXISTS and NOT IN

Introduction

NOT EXISTS and NOT IN should return the rows in a table that are not returned by EXISTS and IN, respectively.

This is true for NOT EXISTS, but when NULLs are encountered, NOT IN will not return the rows not returned by IN.

The following code shows how to get emp who are not managers, so NOT EXISTS and NOT IN are the expressions we can use.

Selecting Employees Who Are Not Managers Using NOT EXISTS

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

Using EXISTS and NOT EXISTS, all of the emp are listed, regardless of the presence of a NULL MGR state for one of the rows.

Selecting Employees Who Are Not Managers Using NOT IN

set feedback on
select ename
from emp
where empno not in (select mgr from emp);

no rows selected

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

1234 NOT IN (1234, NULL)

is equivalent to

1234 != 1234 AND 1234 != NULL.

Each equality check can be evaluated separately and the result would be TRUE AND UNKNOWN.

TRUE AND UNKNOWN is UNKNOWN.

Demo

SQL>
SQL>-- from www .  j  a  va2  s  . com
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>
SQL>
SQL> select e1.ename
  2  from emp e1
  3  where not exists (select e2.mgr
  4                    from   emp e2
  5                    where e1.empno = e2.mgr);

ENAME
--------
BROWN
ADAMS
BREAD
WARD
JONES
MARY
ALLEN
SMITH

8 rows selected.

SQL>
SQL> set feedback on
SQL>
SQL> select ename
  2  from emp
  3  where empno not in (select mgr from emp);

no rows selected

SQL>

Oracle SQL - NULLS with NOT EXISTS and NOT IN

Introduction

Demo

Related Topic