Processing of null by exists not exists in not in oralce

1. First, we will discuss the existence of null in and not in. The SQL statement is as follows:

 1 select 1 result1 from dual where 1 not in (2, 3); 2  3  4 select 1 result2 from dual where 1 not in (2, 3, null); 5  6  7 select 1  result3 from dual where 1  in (2, 3, null, 1); 8  9 10 select 1 result4 from dual where 1  in (2, 3, null);

 

Execution result:

Result1	Result2	Result3	Result4
1	No return value	1	No return value

 

Note: In and not in will compare with the values in the brackets to determine whether the results are equal. in Oracle, null cannot be compared, you can only judge whether it is null or is not null. This causes false to be returned when the in and not in are compared with null. a In (B, c, d) is equivalent to (a = B) | (A = C) | (A = D), while a not in (B, c, d) is equivalent to (! = B) & (! = C) & (! = D)

• Result1 returns result 1 obviously. 1 is not equal to 2 and 3, similar to (1 <> 2) & (1 <> 3). Therefore, return result 1.
• In result2 (1 <> 2) & (1 <> 3) & (1 <> null) the first two values are true. The comparison between the last 1 and null is false, this is the case when one moves wrong. The final result is false, so no results are returned.
• In result3 (1 = 2) | (1 = 3) | (1 = NULL) | (1 = 1) the first three expressions are false, however, if the last expression is true, the final result is true. Therefore, 1 is returned.
• In result4 (1 = 2) | (1 = 3) | (1 = NULL) All three expressions are false, and the final result is false, no result set is returned.

 

2. Let's take a look at the examples of exists and not exists.

1 select 1 result5 from dual where not exists (select 1 from dual t where t.dummy=null);2 3 select 1 result6 from dual where exists (select 1 from dual t where t.dummy=null);

 

 

Execution result:

Result5	Result6
1	No return value

Note: exists and not exists are equivalent to a logical judgment. exists returns a Boolean value in essence. exists tests whether any data is returned in the associated subquery, if at least one row is returned, exists returns true if it is true, not exists determines whether no data is returned for the associated subquery, and true if no data is returned.

 

• In the result5 query, because null cannot be compared with any value, t does not exist. dummy = NULL, the returned result set of the associated query is null, the result of the not exists logic judgment is true, and 1 is finally queried.
• In the result6 query, T. Dummy = NULL exists. The join query return result set is null, and the logical judgment result is false. In the final outer query, no result set is returned.

 

3. Finally, let's look at an interesting query from the csdn forum.

1 select ‘true‘  from dual where (1,2) not in ((2,3),(2,null));2 3 select ‘true‘ from dual where (2,1) not in ((2,3),(2,null));4 5 select ‘true‘ from dual where (2,1) not in ((2,3),(null,3));6 7 select ‘true‘ from dual where (2,1) not in ((2,3),(null,1));

Note: The binary value not in determines,... where (a, B) Not in (c, d), (e, f) is similar to (a, B )! = (C, d) & (a, B )! = (E, F), compare (A, B) with (c, d) as a coordinate comparison, as long as there is a coordinate pair that is not equal, therefore, the formula above can be extended to (! = C | B! = D) & (! = E | B! = F)

• The final character 'true' can be returned if the query result of Row 3 is true and the result is true.
• If the query result of Row 3 is true or false, the result is false, and no result is returned.
• The final character 'true' can be returned if the query result of Row 3 is true and the result is true.
• If the query result of Row 3 is true or false, the result is false, and no result is returned.

 

4. A summary:

• In a In (B, c, d ,..., null), if there is null in the comparison value in the brackets, check whether there are other values in the comparison value and A. If yes, true is returned; otherwise, false is returned.
• Not in a not In (B, c, d,..., null). If null exists in the brackets, false is returned.
• If exists is null in the correlated query condition of exists, internal query results are not displayed, and exists does not meet the judgment returned by at least one row of result set. Therefore, false is returned.
• If not exists is null in the join query condition of not exists, the internal query will not be able to query the results. It meets the expectation judgment of not exists for no result set returned, so true is returned.

 

5. The above is a summary of my personal opinions. You are welcome to criticize and advise me.

Processing of null by exists not exists in not in oralce