- select * from A where id in(select id from B)
Conclusion: in () is suitable for the situation that the data in table B is smaller than that in table a
- select a.* from A a where exists(select 1 from B b where a.id=b.id)
Exists is used to check whether a subquery will return at least one row of data. The subquery does not actually return any data, but returns the value true or false. Exists specifies a subquery to detect the existence of rows.
Conclusion: exists () is suitable for the case where the data in table B is larger than that in table a
- Differences and application scenarios
The difference between in and exists: if there are fewer result set records in the sub query and the table in the main query is large and has an index, you should use in. On the contrary, if there are fewer main query records in the outer layer and the table in the sub query is large and has an index, you should use exists. In fact, we distinguish between in and exists mainly because of the change in the driving order (which is the key to the performance change). If it is exists, the outer table is used as the driving table and is accessed first. If it is in, the sub query is executed first. Therefore, we will take the rapid return of the driving table as the goal, and we will consider the relationship between the index and the result set, In addition, null is not processed when in.
- Not in and not exists
If not in is used in the query statement, the internal and external tables are scanned for the whole table without index; The sub query of not extsts can still use the indexes on the table. Therefore, no matter which table is large, using not exists is faster than not in.
The use efficiency of exists and in is generally higher than that of in, because in does not go through the index, but it depends on the actual situation. The specific use of in is suitable for the situation where the appearance is large and the inner table is small; Exists is suitable for small appearance and large inner appearance.
This work adoptsCC agreement, reprint must indicate the author and the link to this article