Description of the relationship between Oracle null, In, and exists (not in cannot find the result)

 

My colleague said that the query encountered a strange problem. Two tables did not return results after performing the not in operation. Normally, there should be returned results.

 

I. Problem Reproduction

In general, the reproduction of the problem is good news, and we are most afraid that the problem cannot be reproduced.

 

SQL> connscott/tiger;

Connected.

SQL> descemp

Name null? Type

------------------------------------------------------------------

Empno not nullnumber (4)

Ename varchar2 (10)

Job varchar2 (9)

Mgr number (4)

Hiredate date

Sal number (7,2)

Comm number (7, 2)

Deptno number (2)

 

Copy an EMP table:

SQL> createtable emp1 as select * from EMP;

Table created.

 

We insert some values to the EMP table:

SQL> insertinto EMP (empno, ename) values (8888, 'Dave ');

1 row created.

SQL> commit;

Commitcomplete.

 

Here, only empno and ename are inserted, and others are empty.

 

Perform the following operations on two tables:

 

SQL> selectempno, ename from EMP where job not in (select job from emp1 );

No rowsselected

-- No result set is returned. Under normal circumstances, we should reverse the insert Dave.

 

SQL> selectempno, ename from EMP where job in (select job from emp1 );

 

Empno ename

--------------------

7934 Miller

7900 James

7876 Adams

7369 Smith

7844 Turner

7654 Martin

7521 ward

7499 Allen

7782 Clark

7698 Blake

7566 Jones

 

Empno ename

--------------------

7902 Ford

7788 Scott

7839 king

 

14 rowsselected.

 

 

Change to exists for testing:

SQL> select empno, ename from emp a where not exists (select * From emp1 bwhere B. Job = A. Job );

 

Empno ename

--------------------

8888 Dave

 

SQL> select empno, ename from emp a where exists (select * From emp1 B where B. Job = A. Job );

 

Empno ename

--------------------

7934 Miller

7900 James

7876 Adams

7369 Smith

7844 Turner

7654 Martin

7521 ward

7499 Allen

7782 Clark

7698 Blake

7566 Jones

 

Empno ename

--------------------

7902 Ford

7788 Scott

7839 king

 

14 rowsselected.

 

Using exists is not affected by null.

 

Ii. Problem Analysis

The records we query in the EMP table have null values. When we perform the not in and exists operations, null is used to determine the values. If we change to a non-null field, the operations can be performed normally.

 

SQL> selectempno, ename from EMP where empno not in (select empno from emp1 );

 

Empno ename

--------------------

8888 Dave

 

The non-null field is displayed normally.

 

2.1 null description

 

The online documents are described as follows:

Http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/ SQL _elements005.htm#i59110

 

A condition that evaluates to unknown acts almost like false. for example, a SELECT statement with a condition in the where clausethat evaluates tounknown returns no rows. however, a condition evaluatingto unknown differs from false in that further operations Onan unknown condition evaluation will evaluate to unknown. thus, not false evaluatesto true, but not unknown evaluates to unknown.

 

Table 3-20 shows examples of varous evaluations involving nulls inconditions. If the conditions evaluating to unknown were used in a where clauseof aselect statement, then no rows wocould be returned for that query.

Table 3-20 conditionscontaining nulls

Condition	Value of	Evaluation
A is null	10	False
A is not null	10	True
A is null	Null	True
A is not null	Null	False
A = NULL	10	Unknown
A! = NULL	10	Unknown
A = NULL	Null	Unknown
A! = NULL	Null	Unknown
A = 10	Null	Unknown
A! = 10	Null	Unknown

 

 

2.2 description of in and not in judgment

The in function is equivalent to the operation of = any, and the not in function is equivalent! = All.

 

Logically, in is used to determine the given member set or subquery result set one by one.

For example, select * from T1 where ID in (1, 2, 3, null );

 

Actually executed commands are equivalent:

Select * from T1 where id = 1 or ID = 2 orId = 3 or ID = NULL;

According to the preceding table, id = NULL is unknown. Therefore, records whose column value is null cannot be queried. It is equivalent:

Select * from T1 where id = 1or id = 2 or ID = 3

 

 

The logical relationship of not in can be understood as: Not (x = Y or N = m) is equivalent to X! = Y and n! = M. So:

Select * fromt1 where id not in (1, 2, 3, null );

 

Equivalent

Select * fromt1 where ID! = 1 and ID! = 2 and ID! = 3 and ID! = NULL

 

Based on the above null table, ID! = Null returns unknown. The value is false. Therefore, no matter whether the preceding conditions are true or false, the entire logic is false, so no record is returned.

 

The solution is to filter out null before the in and not in operations.

 

 

2.3 exists description

 

Let's take a look at the execution process of exists:

Select * from T1 where exists (select * From T2 where t2.col1 = t1.col1)

 

Equivalent:

 

For X in (select * from T1)
Loop
If (exists (select * From T2 where t2.col1 = x. col1 ))
Then
Output The recordin x
End if
End Loop

 

Exists (select * From T2 where t2.col1 = x. col1) returns a Boolean value. Not exists only returns non-boolean values for the exists clause, this is essentially different from in and not in (not in is a non-in expression and is converted to another equivalent expression)

 

From the above logic, we can also see that exists statements filter out results that meet the criteria of subqueries by repeating the results set of external queries. Therefore, the number of results sets in the external query has the greatest impact on the execution performance of the statement. Therefore, if the number of results sets in the external query is large, the performance of the exists statement may not be much better.

However, an obvious advantage of using exists is that, if the related fields are indexed, indexes will be used for operations. Instead of a full table scan. When a table is large, the efficiency is certainly higher than in and not in. This is why exists is recommended for SQL writing.

 

Now let's take a look at the previous use of not in to find the results, but with not exits, We can find:

 

SQL> select empno, ename from emp a where not exists (select * From emp1 bwhere B. Job = A. Job );

 

Empno ename

--------------------

8888 Dave

 

Dave's record exists in the EMP table and does not exist in the emp1 table.

 

The preceding query is equivalent:

For X in (select * from EMPA)
Loop
If (not exists (select * From emp2b where B. Job = x. Job)
Then
Output The recordin x
End if
End Loop

 

In this way, when our X. Job is null, the conditions are met and Dave's record is output.

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

WEAVER: http://weibo.com/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 (full) dba5 group: 142216823 (full)

Dba6 group: 158654907 (full) dba7 group: 69087192 (full) dba8 group: 172855474

DBA super group 2: 151508914 dba9 group: 102954821 chat group: 40132017 (full)

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.