Oracle does not have the expected results (NULL, in, exists detailed)

Problem:

Statement 1:

SELECT * FROM table1 A where a.col1 not in (Select col1 from Table2 B)

Reprint Annotated Source: http://x-spirit.iteye.com/, http://www.blogjava.net/zhangwei217245/
If so, there should have been a piece of data, the result is not.
If I rewrite it like this:

Statement 2:

SELECT * FROM table1 A where isn't exists (SELECT * from table2 B where b.col1 = a.col1)

The result is correct and there is a piece of data showing.
Reprint Annotated Source: http://x-spirit.iteye.com/, http://www.blogjava.net/zhangwei217245/

After a search, it was thought that the result set of the subquery was too large.

Then there is online strongman pointing out: There is an empty set in the subquery. That is, a null result in the result set of the subquery.

Change the query statement to:

Statement 3:

SELECT * FROM table1 A where a.col1 not in (Select col1 from table2 B where b.col1 are NOT null )

Sure enough to find out. And not bad ... Awesome! ~ ~


The following is an analysis of this topic:

1. First of all, NULL in Oracle.

Null in Oracle represents a meaningless or no value. Null and other values are logically calculated, and null behaves more like false during the operation.
Here's the truth table:

	and NULL	OR NULL
TRUE	Null	TRUE
FALSE	FALSE	Null
Null	Null	Null

In addition, null and other values are compared or arithmetic operations (<, >, =,! =, + 、-、 *,/), and the result is still null.

If you want to determine whether a value is NULL, you can use is null or is not NULL.

2. Again, in the Oracle.

In is a member condition that compares each member value for a given collection or subquery.
In function is equivalent to =any operation, while not in functionally equivalent to!=all operation.
In is logically actually a given set of members or sub-query result sets, such as:

SELECT * FROM table1 A WHERE a.col1 in (+, NULL);

is actually executed.

SELECT * FROM table1 A WHERE a.col1 = or a.col1 = or a.col1 = NULL;

Thus, depending on the operation characteristics of NULL and the truth table, we can see that the WHERE clause above can be simplified (if NULL is returned without a result set, which is the same as false) for

WHERE a.col1 = OR A.col1 = 50

That is, if you really have a col1 column with a null value in your table1, the statement cannot be queried for records that have null values.

Let's look at not in. According to the logical operation relationship, we know that not (x=y OR n=m) is equivalent to X!=y and n!=m, then:

SELECT * FROM table1 A WHERE a.col1 not in (+, NULL)

Equivalent to

SELECT * FROM table1 A WHERE a.col1! = and a.col1! = and a.col1! = NULL

According to the null operator and truth table, the result must be null or FALSE, regardless of whether the first two criteria are true. So there is absolutely no record to return.

This is why statement 1 does not find the results due. Of course, if you do not use in the time, in advance in the subquery to remove NULL, then there is no problem, such as statement 3.
Some children's shoes may have to ask: if I want to put in the table in the same as the col1 column of the same value of the records are detected, even if a, b two tables inside the col1 column contains a value of NULL records, with this statement can not be done?

I can only regret to tell you that if you want to simply use in after where it seems unlikely, of course, you can put the null condition in the external query statement, for example:

SELECT * FROM table1 A WHERE a.col1 in (SELECT b.col1 from table2 B) OR a.col1 is NULL;

Reprint Annotated Source: http://x-spirit.iteye.com/, http://www.blogjava.net/zhangwei217245/

3. Finally, talk about exists.

Some people say that exists performance is better than in. But this is very one-sided. Let's take a look at exists's execution process:

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 RECORD in X
End If
End Loop

Reprint Annotated Source: http://x-spirit.iteye.com/, http://www.blogjava.net/zhangwei217245/
In other words, the EXISTS statement actually filters out the result set that conforms to the subquery standard by looping the result set of the outer query. So the number of result sets of an external query has the greatest impact on the execution performance of the statement, so if the number of result sets of the external query is large, the performance of the EXISTS statement will not necessarily be much better.
Reprint Annotated Source: http://x-spirit.iteye.com/, http://www.blogjava.net/zhangwei217245/
Of course, some people say that not in is the external query and subqueries have done a full table scan, if there is an index, but also not indexed, but not exists is to do the connection query, so, if the two columns of the connection query are indexed, performance will be a certain increase.
Of course, as for the actual query efficiency, I would like to be specific analysis of the situation.


Then we might as well analyze the statement 2 Why you can get the right result:

The statement 2 is this:

SELECT * FROM table1 A where isn't exists (select B.col1 from table2 B where b.col1 = a.col1)

This is actually the execution process:

For x in (SELECT * FROM table1 A)
Loop
if (Not EXISTS (SELECT * from table2 B where b.col1 = x.col1)
Then
OUTPUT the RECORD in X
End If
End Loop

Because table A does not contain NULL records, traversing table A will only be able to pick out the unique records in table A.

This is why statement 2 is able to complete the task of statement 3.

But what if there is a null record in table A and table B does not exist?
Answer: Null in Table A will also be detected. Because select * from table2 B where b.col1 = null does not return a result,
The value of NOT EXISTS (SELECT * from table2 B where b.col1 = X.col1) is true.

Oracle does not have the expected results (NULL, in, exists detailed)