Understand how null affects in and exits statements

Author: builder.com
Thursday, October 14 2004 pm

On the surface, SQL statements of in and exits are interchangeable and equivalent. However, they are very different in processing uull data and lead to different results. The root cause of the problem is that in an Oracle database, a null value means an unknown variable. Therefore, the result of operating the Null Value Comparison function is also an unknown variable, in addition, any null value returned is usually ignored. For example, none of the following queries will return the value of a row: <Br/> select 'true' from dual where 1 = null; select 'true' from dual where 1 != null;   Only if is null can return true and return a row: select 'true' from dual where 1 is null; select 'true' from dual where null is null;   When you choose to use in, you will tell SQL to select a value and compare it with each other. If the null value exists, no row is returned, even if both 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));

An in statement is functionally equivalent= Any statement:

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));

 

When you use an exists equivalent statement, SQL calculates all rows and ignores the values in the subquery.

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

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

 

INIt is logically the same as exists. The in statement compares the values returned by the subquery and filters some rows in the output query. The exists statement compares the values of rows and filters some rows in the subquery. For null values, the row results are the same.

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

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

 

However, when the logic is used in reverse order, that is, not in and not exists, the problem arises:

selectename from emp where empno not in (select mgr from emp);

selectename from emp e where not exists (select 0 from emp where mgr =

e.empno);

 

NOT INThe statement is essentially equivalent to using = to compare each value. If the test is false or null, the comparison fails. 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));

These queries do not return any rows. The second query statement is more obvious, that is1 != null, So the entire where is false. However, these query statements can be changed:

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

select 'true' from dual where 1 != 2 and 1 != 3;

 

You can also use the not in query, as long as you ensure that the returned value does not contain null values:

selectename from emp where empno not in (select mgr from emp where mgr is not

null);

selectename from emp where empno not in (select nvl(mgr,0) from emp);

 

Understanding in,Exists, not in, and not exists. When null appears in any subquery, you can avoid some common problems.

 

Scott Stephen has been working in Oracle for more than 13 years. His work covers technical support, e-commerce, market development, and software development.