IN, EXISTS

Oracle Tip: Understand how NULLs affect IN and EXISTS

On the surface, it may appear that the SQL clauses IN and EXISTS are interchangeable. However, they're quite different in how they handle NULL values and may give different results. The problem comes from the fact that, in an Oracle database, a NULL value means unknown, so any comparison or operation against a NULL value is also NULL, and any test that returns NULL is always ignored. For example, neither one of these queries return any rows:

select 'true' from dual where 1 = null;
select 'true' from dual where 1 != null;

SQL> select 'true' from dual where 1 = null;
선택된 레코드가 없습니다.

SQL> select 'true' from dual where 1 != null;
선택된 레코드가 없습니다.

The value 1 is neither equal nor not equal to NULL. Only IS NULL would return true on a NULL value and return a row.

select 'true' from dual where 1 is null;
select 'true' from dual where null is null;

SQL> select 'true' from dual where 1 is null;
선택된 레코드가 없습니다.

SQL> select 'true' from dual where null is null;
'TRU
----
true

When you use IN, you're telling SQL to take a value and compare it against every value or set of values in a list using =. If any NULL values exist, a row will not be returned--even if both values are NULL.

select 'true' from dual where null in (null);
select 'true' from dual where (null,null) in ((null,null));
select 'true' from dual where (1,null) in ((1,null));

SQL> select 'true' from dual where null in (null);
선택된 레코드가 없습니다.

SQL> select 'true' from dual where (null,null) in ((null,null));
선택된 레코드가 없습니다.

SQL> select 'true' from dual where (1,null) in ((1,null));
선택된 레코드가 없습니다.

An IN is functionally equivalent to the = ANY clause:

select 'true' from dual where null = ANY (null);
select 'true' from dual where (null,null) = ANY ((null,null));
select 'true' from dual where (1,null) = ANY ((1,null));

SQL> select 'true' from dual where null = ANY (null);
선택된 레코드가 없습니다.

SQL> select 'true' from dual where (null,null) = ANY ((null,null));
선택된 레코드가 없습니다.

SQL> select 'true' from dual where (1,null) = ANY ((1,null));
선택된 레코드가 없습니다.

When you use an equivalent form of EXISTS, SQL counts rows and ignores the value(s) in the subquery--even if you return NULL.

select 'true' from dual where exists (select null from dual);
select 'true' from dual where exists (select 0 from dual where null is null);

SQL> select 'true' from dual where exists (select null from dual);
'TRU
----
true

SQL> select 'true' from dual where exists (select 0 from dual where null is null);
'TRU
----
true

The IN and EXISTS are logically the same. The IN clause compares values returned by the subquery and filters out rows in the outer query; the EXISTS clause compares values and filters out rows inside the subquery. In the case of NULL values, the resulting set of rows is the same.

select ename from emp where empno in (select mgr from emp);
select ename from emp e where exists (select 0 from emp where mgr = e.empno);

SQL> select ename from emp where empno in (select mgr from emp);
ENAME
----------
FORD
BLAKE
KING
JONES
SCOTT
CLARK

SQL> select ename from emp e where exists (select 0 from emp where mgr = e.empno);
ENAME
----------
FORD
BLAKE
KING
JONES
SCOTT
CLARK

But problems arise when the logic is reversed to use NOT IN and NOT EXISTS, which return different sets of rows (the first query returns 0 rows; the second returns the intended data--they aren't the same query):

select ename from emp where empno not in (select mgr from emp);
select ename from emp e where not exists (select 0 from emp where mgr = e.empno);

SQL> select ename from emp where empno not in (select mgr from emp);
선택된 레코드가 없습니다.

SQL> select ename from emp e where not exists (select 0 from emp where mgr = e.empno);
ENAME
----------
TURNER
WARD
MARTIN
ALLEN
MILLER
SMITH
ADAMS
JAMES

The NOT IN clause is virtually equivalent to comparing each value with = and failing if any test is FALSE or NULL. For example:

select 'true' from dual where 1 not in (null,2);
select 'true' from dual where 1 != null and 1 != 2;
select 'true' from dual where (1,2) not in ((2,3),(2,null));
select 'true' from dual where (1,null) not in ((1,2),(2,3));

SQL> select 'true' from dual where 1 not in (null,2);
선택된 레코드가 없습니다.

SQL> select 'true' from dual where 1 != null and 1 != 2;
선택된 레코드가 없습니다.

SQL> select 'true' from dual where (1,2) not in ((2,3),(2,null));
'TRU
----
true

SQL> select 'true' from dual where (1,null) not in ((1,2),(2,3));
선택된 레코드가 없습니다.

These queries don't return any rows. The second is more obvious, 1 != NULL is NULL, so the whole WHERE condition is false for that row. While these would work:

select 'true' from dual where 1 not in (2,3);
select 'true' from dual where 1 != 2 and 1 != 3;

SQL> select 'true' from dual where 1 not in (2,3);
'TRU
----
true

SQL> select 'true' from dual where 1 != 2 and 1 != 3;
'TRU
----
true

You can still use the NOT IN query from before, as long as you prevent NULL from being returned in the results (again, these both work, but I'm assuming empno is not null, which is a good assumption in this case):

select ename from emp where empno not in (select mgr from emp where mgr is not null);
select ename from emp where empno not in (select nvl(mgr,0) from emp);

SQL> select ename from emp where empno not in (select mgr from emp where mgr is not null);ENAME
----------
TURNER
WARD
MARTIN
ALLEN
MILLER
SMITH
ADAMS
JAMES

SQL> select ename from emp where empno not in (select nvl(mgr,0) from emp);
ENAME
----------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER

By understanding the difference between IN, EXISTS, NOT IN, and NOT EXISTS, you can avoid a very common problem when NULLs appear in the data of a subquery.