Oracle in and exist Condition Analysis

When writing SQL statements, I often have troubles using in or exists. In terms of performance, how can I make the fastest choice?
　　
This is my reading experience. For more details, see the link at the end.
　　
If in is used, the execution process is as follows:
　　
Select * from T1 where X in (select y from T2)
　　
Like:
　　
Select *
From T1, (select distinct y from T2) T2
Where t1.x = t2.y;
　　
If exists is used, as in the preceding query result, we rewrite it:
　　
Select * from T1 where exists (select null from T2 where Y = X)
　　
Like:
　　
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
　　
So think about the difference. When the subquery table stores a large amount of data, the efficiency of the first method will be poor, because his execution plan uses sort merge join, if the table of the subquery is relatively small, the execution efficiency of using in should be better.
　　
If external tables (such as T1) store a large amount of data, then? The efficiency of using the first method in will be better. If you use exists, in addition to performing full scan on the bigtable T1, you will also read all data columns of T1, performance is naturally poor. In a simple sentence, the outer, inner, and inner are equal to exists. This is a practical Rough Evaluation Method and is applicable in most cases.

 

 

 

There are two simple examples to illustrate the efficiency of "exists" and "in ".
1) Select * from T1 where exists (select 1 from T2 where t1.a = t2.a );

Small Data size of T1 and large data size of T2, t1 <t2, 1) high query efficiency.

2) Select * from T1 where t1.a in (select t2.a from T2 );

T1 has a large data volume and T2 has an hour, T1> T2, 2) High query efficiency.

Exists usage:

Note: 1) the section in the sentence contains a color font to understand its meaning;

"Select 1 from T2 where t1.a = t2.a" is equivalent to a join Table query, which is equivalent

"Select 1 from T1, T2 where t1.a = t2.a"

However, if you execute the statement in parentheses (1), a syntax error will be reported, which is also worth attention when using exists.

"Exists (XXX)" indicates whether the statement in parentheses can identify the record and whether the record to be queried exists.

Therefore, the "1" in "select 1" is irrelevant. It is okay to replace it with "*". It only cares whether the data in the brackets can be searched out, whether such a record exists. If so, the where condition of the sentence is true.

Usage of in:

Continue to reference the above example

"2) Select * from T1 where t1.a in (select t2.a from T2 )"

The content of the field searched by the statement following the "in" must correspond to each other. Generally, the expression of field a in Table T1 and table t2 must be the same, otherwise, this query is meaningless.

For example, table T1 and table T2 have a field indicating the ticket number. However, table T1 indicates that the ticket number field is named "ticketid" and table T2 indicates "ID ", however, the expression is the same, and the data format is the same. In this case, use the 2) method as follows:

"Select * from T1 where t1.ticketid in (select t2.id from T2 )"

Select name from employee where name not in (Select name from student );

Select name from employee where not exists (Select name from student );

The first SQL statement is less efficient than the second statement.

By using exists, Oracle first checks the primary query, and then runs the subquery until it finds the first match, which saves time. When Oracle executes the in subquery, it first executes the subquery and stores the obtained result list in a temporary table with an index. Before executing a subquery, the system suspends the primary query. After the subquery is executed, it is stored in the temporary table and then executes the primary query. This is why exists is faster than in queries.

If the list in (...) is "large data volume", I'm afraid the landlord must pay attention to the restrictions :)
9i is limited to 1000 ~~~~ Otherwise, an error occurs.