Summary of optimization principles of IN & amp; EXISTS and not in & amp; NOT EXISTS

1. Execution Process of EXISTS
Select * from t1 where exists (select null from t2 where y = x)
It can be understood:
Copy codeThe Code is as follows:
For x in (select * from t1)
Loop
If (exists (select null from t2 where y = x. x)
Then
OUTPUT THE RECORD
End if
End loop

Performance differences between in and exists:
If the subquery results have fewer records, the primary query tables are large and indexed, The in statement should be used. If the outer primary query has fewer records, exists is used when the table in the subquery is large and there is an index.
In fact, we distinguish between in and exists mainly because of changes in the driving sequence (this is the key to performance changes). If exists is used, the External table is the driving table and is first accessed, if it is IN, execute the subquery first, so we will take the fast return of the driving table as the goal, then the relationship between the index and the result set will be taken into account.
IN addition, the IN clause does not process NULL, for example:
Select 1 from dual where null in (0, 1, 2, null)
The result is null.

2. not in and not exists:
Not exists Execution Process
Copy codeThe Code is as follows:
Select .....
From rollup R
Where not exists (select 'found 'from title T
Where R. source_id = T. Title_ID );

It can be understood:
Copy codeThe Code is as follows:
For x in (select * from rollup)
Loop
If (not exists (that query) then
OUTPUT
End if;
End;

Note: not exists and not in cannot be completely replaced, depending on the specific requirements. If the selected column can be empty, it cannot be replaced.
For example, the following statement shows their differences:
Select x, y from t;
X y
------------
1 3
3 1
1 2
1 1
3 1
5
Select * from t where x not in (select y from t t2)
No rows
Select * from t where not exists (select null from t t2
Where t2.y = t. x)
X y
------------
5 NULL
Therefore, you must determine the specific requirements.
Performance differences between not in and not exists:
Not in is used only when the field after the select keyword in the subquery has a not null constraint or such suggestion. in addition, if the table in the primary query is large, if the table in the subquery is small but has many records, use not in and anti hash join.
If the primary query table contains fewer records, the subquery table contains more records, and an index, you can use not exists, in addition, it is best to use/* + HASH_AJ */or external connection + is null for not in.
Not in is better IN cost-based applications
For example:
Copy codeThe Code is as follows:
Select .....
From rollup R
Where not exists (select 'found 'from title T
Where R. source_id = T. Title_ID );

Change to (good)
Select ......
From title T, rollup R
Where R. source_id = T. Title_id (+)
And T. Title_id is null;
Or (good)
SQL> select/* + HASH_AJ */...
From rollup R
Where ource_id not in (select ource_id
From title T
Where ource_id is not null)
Note: The above is just a theoretical suggestion. The best principle is that you can use the execution plan to analyze the above and get the best statement writing method.
'// ====================================
Exists, not exists Summary

1 exists
SELECT * FROM anken_m where exists (
SELECT my_list_temp_m.sales_code
FROM my_list_temp_m
WHERE my_list_temp_m.sales_code = anken_m.sales_code)
Note:
1) query the sales_code in the anken_m and my_list_temp_m tables.
2) sales_code is the primary key of anken_m and the foreign key of my_list_temp_m.
Note:
1) anken_m is the query object.
2) The inner query table my_list_temp_m is a condition object.
3) the query tables at the internal and external layers cannot be the same.
4) The anken_m table as the join condition does not need to be added after the FROM query in the internal layer.
5) The order of the condition my_list_temp_m.sales_code = anken_m.sales_code does not affect the query results.

2 not exists
SELECT * FROM anken_m where not exists (
SELECT my_list_temp_m.sales_code
FROM my_list_temp_m
WHERE my_list_temp_m.sales_code = anken_m.sales_code)
Note:
1) query the sales_code that exists in the anken_m table but does not exist in the my_list_temp_m table.
2) sales_code is the primary key of anken_m and the foreign key of my_list_temp_m.
Note:
1) anken_m is the query object.
2) The inner query table my_list_temp_m is a condition object.
3) the query tables at the internal and external layers cannot be the same.
4) The anken_m table as the join condition does not need to be added after the FROM query in the internal layer.
5) The order of the condition my_list_temp_m.sales_code = anken_m.sales_code does not affect the query results.

3 comprehensive application
UPDATE anken_m
SET (plan_type_code, branch_name, business_type_code)
= (SELECT anken. plan_type_code, anken. branch_name, anken. business_type_code
FROM anken
WHERE anken. sales_code = anken_m.sales_code)
Where exists (
SELECT anken. sales_code
FROM anken, my_list_temp_m
WHERE my_list_temp_m.sales_code = anken. sales_code
AND anken. sales_code = anken_m.sales_code
)
Note:
1) use the record data of one table to update the record data of another table.
2) use an SQL statement for batch update.
2) sales_code is the anken, the primary key of anken_m, and the foreign key of my_list_temp_m.
Note:
1) The fields to be updated in the set statement must correspond to the data source fields one by one, and the conditions in the Data Source Query must be limited to one record. That is, according to sales_code, You can uniquely identify a record of anken and a record of anken_m, so that the record to be updated is the same as the primary key of the data source record.
2) The where exists statement can be used to determine the range of data source records, that is, which records in the anken table can be used to update the anken_m table. Therefore, anken_m does not need to be added after FROM in the where exists statement.