Comparison Between in and exists in Oracle

Exists Execution Process
Select * from T1 where exists (select null from T2 where Y = X)
It can be understood:
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, null is not processed during in.
For example:
Select 1 from dual where NULL in (0, 1, 2, null)

Null

 

2. Not in and not exists:
Not exists Execution Process
Select .....
From rollup R
Where not exists (select 'found 'from Title t
Where R. source_id = T. title_id );
It can be understood:
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:
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 on the basis of the above, you can use the execution plan for analysis to get the best statement writing method.
I hope you will raise your objection.