In and exists usage and performance analysis (i): use in

This section focuses on the principles and usage of in, as well as the common erroneous viewpoints.

First, in the use
Use of 1.1 in
The 1.1.1 and outer Two Table association fields are not empty
Sql> select * from TB1;
T1 NAME1
---------- ----------
1 1
2 2
Sql> select * from TB2;
T2 NAME2
---------- ----------
2 2
Sql> Select COUNT (1) from tb1 where T1 to (select T2 from TB2);
--t1=2 Records meet the criteria and the results are in line with conventional thinking
COUNT (1)
----------
1
Sql> Select COUNT (1) from tb1 where T1 isn't in (select T2 from TB2);
--t1=1 Records meet the criteria and the results are in line with conventional thinking
COUNT (1)
----------
1
1.1.2 When there is no null value for the outer-association field and the Inner Table association list has null values
Sql> select * from TB1;
T1 NAME1
---------- ----------
1 1
2 2
Sql> select * from TB2;
T2 NAME2
---------- ----------
2 2
3
Sql> Select COUNT (1) from tb1 where T1 to (select T2 from TB2);
--t1=2 Records meet the criteria and the results are in line with conventional thinking
COUNT (1)
----------
1
Sql> Select COUNT (1) from tb1 where T1 isn't in (select T2 from TB2);
--No records meet the conditions, the results are not consistent with conventional thinking
COUNT (1)
----------
0
Sql> Select COUNT (1) from tb1 where T1 into (select T2 from TB2 where T2 are not null);
--t1=2 Records meet the criteria and the results are in line with conventional thinking
COUNT (1)
----------
1
Sql> Select COUNT (1) from tb1 where T1 isn't in (select T2 from TB2 where T2 be not null);
--t1=2 Records meet the criteria and the results are in line with conventional thinking
COUNT (1)
----------
1
1.1.3 When the outer correlation field has null values and the Inner Table association list has no null value
Sql> select * from TB1;
T1 NAME1
---------- ----------
1 1
1
2 2
Sql> select * from TB2;
T2 NAME2
---------- ----------
2 2
3 3
Sql> Select COUNT (1) from tb1 where T1 to (select T2 from TB2);
--t1=2 records conform to query criteria and are consistent with conventional thinking
COUNT (1)
----------
1
Sql> Select COUNT (1) from tb1 where T1 isn't in (select T2 from TB2);
-Only t1=2 records meet the query criteria, contrary to conventional thinking
COUNT (1)
----------
1
1.1.4 when both inside and outside of the associated field have null values
Sql> select * from TB1;
T1 NAME1
---------- ----------
1 1
2 2
3
Sql> select * from TB2;
T2 NAME2
---------- ----------
2 2
3
Sql> Select COUNT (1) from tb1 where T1 to (select T2 from TB2);
--only t1=2 records meet the conditions, contrary to conventional thinking
COUNT (1)
----------
1
Sql> Select COUNT (1) from tb1 where T1 isn't in (select T2 from TB2);
--no record of qualifying, contrary to conventional thinking
COUNT (1)
----------
0
Sql> Select COUNT (1) from tb1 where T1 into (select T2 from TB2 where T2 are not null);
--only t1=2 records meet the criteria and are in line with conventional thinking
COUNT (1)
----------
1
Sql> Select COUNT (1) from tb1 where T1 isn't in (select T2 from TB2 where T2 be not null);
--only t1=1 records meet the conditions, contrary to conventional thinking
COUNT (1)
----------
1
1.1.5 appearance no record
Sql> select * from TB1;
T1 NAME1
---------- ----------
Sql> select * from TB2;
T2 NAME2
---------- ----------
2 2
7 ·
Sql> Select COUNT (1) from tb1 where T1 to (select T2 from TB2);
--No records returned
COUNT (1)
----------
0
Sql> Select COUNT (1) from tb1 where T1 isn't in (select T2 from TB2);
--No records returned
COUNT (1)
----------
0
It is easy to understand that the index returns a record number of 0, regardless of whether the condition is true or not, because the appearance has no records
No record in 1.1.6 table
Sql> select * from TB1;
T1 NAME1
---------- ----------
1 1
2 2
Sql> select * from TB2;
T2 NAME2
---------- ----------
Sql> Select COUNT (1) from tb1 where T1 to (select T2 from TB2);
--all records are not eligible and the results are in line with conventional thinking
COUNT (1)
----------
0
Sql> Select COUNT (1) from tb1 where T1 isn't in (select T2 from TB2);
--all records are qualified and the results are in line with conventional thinking
COUNT (1)
----------
2
1.2 In Operation Summary:
1.2.1 Single row as in operation
1. Col in (V1,v2,... vn) is equivalent to (COL=V1 or col=v2 or ... col=vn)
Col not in (v1,v2,... vn) is equivalent to (COL&LT;&GT;V1 and Col<>v2 and ... col<>vn)
2. When the number of records in the in list is greater than 0 and the in list (VN) has a null value, Oracle converts in the process in:
(COL=V1 or Col=v2 or Col=null or ... col=vn)
Therefore, a null value in the in list does not cause a result that is not the same as conventional thinking
3. When the number of records in the not in list is greater than 0 and the not in list has a null value, Oracle converts to:
(Col<>v1 and Col<>v2 and Col<>null and ... col<>vn), because null in Oracle is a very special value, Null returns false as an arithmetic operator comparison of any value, because multiple conditions are connected with and, so the entire condition is false and no records are returned.
This situation is the easiest to confuse and not understand.
4. When the number of records in the In/not in list is greater than 0, and the col is null, NULL returns false as an arithmetic operator comparison of any value, and it is known in the process of in and not, that no records are returned by Oracle, whether in or not. That is to say, Oracle is processing the equivalent of ignoring COL records as null.
5. When the number of records in the In/not in list is 0 o'clock (note that the In/not in list is all empty), the in operation corresponds to the condition 1=2;not in operation equivalent to 1=1;
6. In the not in operation, you can add Col is not null to a subquery to allow records to return
1.2. More than 2 columns as in operations
1. (col1,col2) in (V11,V12), (v21,v22),... (VN1,VN2)) Equivalent
((Col1=v11 and COL2=V12) or (COL1=V21 and col2=v21) ... or (COL1=VN1 and COL2=VN2))
(col1,col2) Not in ((V11,V12), (v21,v22),... (VN1,VN2)) Equivalent
((col1<>v11 or COL2&LT;&GT;V12) and (COL1&LT;&GT;V21 or col2<>v22) and ... (COL1&LT;&GT;VN1 or COL2&LT;&GT;VN2))
2. Multi-column in operations when the principle and a single column, the Col1 and col2 as a whole col, the vn1 and vn2 as a whole vn is equivalent to a single row in the operation.
3. Multi-column operations are more special when both col1 and col2 are null, the equivalent of a single column is col to null,oracle in dealing with this situation, the equivalent of ignoring this record;
When both VN1 and vn2 are null, the equivalent of VN null in a single-column operation does not affect the return result when in operation, but no records are returned when the not in operation is made.
4. When one of the col1 and col2 is NULL, Oracle is equivalent to ignoring the record in the in operation because (null or null or null ...) The result is null. (not in cannot be ignored)
5. When one of the col1 and col2 is null and the other is contained in the Not in list, Oracle is equivalent to ignoring the record (null && true/false result is null) in the not in operation.
6. In the not in operation, you can add in a subquery ((col1 is not null) or (col2 are not null) to allow results to return
7. The above summary applies to cases where the number of records in the In/not in list is greater than 0
8. Any, all operations are similar to in