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
SQL> SQL>-- from w w w . j a v a 2 s. c o m 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>