Comparison of in and exists Processes

The execution procedures are completely different.

In Process

Select * From tablea A where a. ID in (select B. a_id from tableb B );

1) The subquery first queries all the aid and result set listb in table B.

2) perform an external query and the result set is lista.

3) List and listb take Cartesian product, that is, there are lista. Len * listb. Len records. Filter Cartesian product results based on A. ID = B. a_id.

For (T in lista. Len * listb. Len ){

If (T. ID = T. Aid ){

List. Add (t );

}

}

Retrun list;

So,The efficiency of in depends on in subqueries.

 

Exists Process

Select * From tablea A where exists (select 1 from tableb B where a. ID = B. a_id );

1) For external queries, select * From tablea A, and the result set lista.

2) perform exists filtering on the. ID of lista.

For (A in lista. Length ){

If (select 1 from tableb B where B. a_id = A. ID )! = NULL ){

List. Add ();

}

}

So,The efficiency of exists depends on the external query.

 

Summary

When the result set of a subquery is relatively large, do not useIn.

So, unless the sub-The in query result set is very small (such as a dictionary), and exists (Cartesian product without in) is generally used first ).

 

Not in and not exists

AlthoughThe statement "using exists is generally better than using in" is not necessarily accurate,

However"In general, using not exists is better than using not in.

UseNot in scans the External table and internal table, and ignores the index;

UseNot exists subqueries can use table indexes.

Therefore, whether the subquery result set is large or small, useNot exists Aspect RatioNot in .

 

Reference:

Https://www.cnblogs.com/liyasong/p/ SQL _in_exists.html

 

Comparison of in and exists Processes