About in and exist in Oracle

Sometimes a column is compared with a series of values. The simplest way is to use subqueries in the where clause. In the WHERE clause, you can use subqueries in two formats.

1. Use the in Operator

2. Use the exist Operator

The first format is easier to write, and the second format is more efficient than the first format. In Oracle, almost all in operator subqueries can be rewritten to subqueries using exist.

Differences between in and exist:

Use the exist clause to query the data extracted from the table without a pipe. It only displays the WHERE clause. In this way, the optimizer does not have to traverse the entire table, but only performs the work based on the index (Here we assume that the column used in the where statement has an index ). Compared with the in clause, exist uses connected subqueries, which is more difficult to construct than in subqueries.

By using exist, the Oracle system first checks the primary query, and then runs the subquery to find the first match, which saves time.

When executing an in subquery, the Oracle system 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 exist is faster than in queries.

Note: A null value exists in not in, and the query result must be null.