Understand how null affects in and exits statements

Statement
On the surface, the SQL statements in and exits are interchangeable and equivalent. However, they can make a big difference in processing uull data and lead to different results. The root of the problem is that in an Oracle database, a null value means an unknown variable, so the result of the comparison function that operates a null value is an unknown variable, and any value that returns null is usually ignored. For example, the following query does not return a row of values:

Select ' True ' from dual where 1 = null;

Select ' True ' from dual where 1!= null;



Only is null to return True, and returns 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 value. If a null value exists, a row will not be returned, even if all two 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 to = 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 a 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 to dual where null is NULL);



In and exists are logically the same. In statement to compare the values returned by the subquery and filter some rows in the output query. The EXISTS statement compares the values of the rows and filters some rows in the subquery. For a null value, the result of the row is 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 logic is used in reverse, that is, not in and not exists, the problem arises:

Selectename from EMP where empno is not in (select Mgr from EMP);

Selectename from EMP e where NOT EXISTS (select 0 from emp where mgr =

E.EMPNO);



The not in statement is essentially equivalent to using = to compare each value, and if the test is false or NULL, the result is a comparison failure. For example:

Select ' True ' from dual where 1 isn't in (null,2);

Select ' True ' from dual where 1!= null and 1!= 2;

Select ' True ' from dual where (1,2) isn't in ((2,3), (2,null));

Select ' True ' from dual where (1,null) isn't in ((1,2), (2,3));


These queries do not return any row. The second query statement is more obvious, that is, 1!= null, so the entire where is false. However, these query statements are variable:

Select ' True ' from dual where 1 isn't 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 guarantee that the returned value does not appear null values:

Selectename from EMP where empno isn't in (select Mgr the from EMP where Mgr are not

NULL);

Selectename from EMP where empno to (select NVL (mgr,0) from EMP);



By understanding the difference between in,exists, not in, and not EXISTS, you can avoid some common problems when NULL is present in either subquery.



Scott Stephens has been working for Oracle for more than 13 years, and his areas of work include technical support, e-commerce, market development, and software development.