The difference between exists and in of a database SQL statement

The key to performance changes:

#1 the order of execution who is the driver table, who executes the query first, who executes the query after the execution of the process exists the advantage is: as long as the existence of the return, so there is may not be requiredScan the entire table. In needs to scan the entire table and return the result. Therefore, in the case of a smaller Word table, scanning the whole table and some of the table basically no difference, but in the case of large tables, exists will have an advantage. Look at these two statements:--The subquery performs a full association and returns all eligible city_id

SELECT * from areas where ID in (select city_id from deals where deals.city_id = areas.id);

--The correlation of sub-query is actually the same, but the subquery can only find a result, return, so the efficiency is still relatively high

SELECT * from areas where exists (select null from deals where deals.city_id = areas.id);

The result of the query of the #3 Word table exists that the result of the subquery is not present, but what results are found, what fields do not care; In need subquery to find the results to the main query using for inchAnd existsDifferences in Performance:
If the subquery results in fewer result set records, the table in the main query should be large and indexed. inch, and if the Outer main query record is less, the table in the subquery is large and the index is used exists。
In fact, we distinguish inchAnd existsThe main cause is the change in the driving sequence (this is the key to performance change), if it is exists, then the other layer table is the driver table, which is accessed first, if it is inch, the subquery is executed first, so we will target the fast return of the driver table, then we will consider the relationship between the index and the result set. inchNULL is not processed. Note: not EXISTSWith not inchCan not completely replace each other, to see the specific needs. If the selected column can be empty, it cannot be replaced.
For not inchAnd not existsDifferences in Performance:
Not inchOnly if the field after the SELECT keyword has a NOT NULL constraint in the subquery, or if there is such a hint, use not inch, and if the table in the main query is large, the table in the subquery is small but there are many records, you should use not inchand use the anti hash join.
If there are fewer records in the main query table, there are many records in the subquery table, and there are indexes, you can use not exists, in addition not inchIt is best to use/*+ Hash_aj */or outer connection +is NULL
Not inchBetter in cost-based applications

The difference between exists and in of a database SQL statement