About the difference between in and exists in Oracle __oracle

Take Oracle Database for example

1 use of exists

exists is used only for subqueries, can be substituted in, and if matched to result, exit internal

query, and returns the entire result data with the condition flag to true, in whether or not the match is matched to

all matches are completed , the subquery results can be fixed as constants without affecting the query effect by using exists.

and high efficiency. If you query the names of all sales department employees, compare the following:

In is often better if the results of the subquery are very small

When your write a query using the IN clause, you ' re telling the Rule-based Optimizer

Want the inner query to drive the outer query.

When you write EXISTS in a WHERE clause, your ' re telling the optimizer that you want the outer

Query to is run, using each value to fetch a value from the inner query.

In many cases, EXISTS are better because it requires you to specify a join condition, which can

Invoke an INDEX scan. However, in is often better if the results of the subquery are

Small. You are usually want to run this query that returns the smaller set of results.

In and exists contrast:

If the subquery result set is relatively small, the priority is to use in, if the outer query is smaller than the subquery, give priority to

with exists. Because if you use in, Oracle will first query the subquery and then match the outer query.

If you use exists, Oracle will first query the outer table and then match the inner table. Optimization

Matching principle, take the minimum record to match the big record.

in the descend Web document, how to say:

First look at the grammar, the syntax is very simple, look at the example everyone will understand, but must pay attention to operation inside the sentence is very important, because this is related to the null problem, is to return at least one row to return true value.

2 use of NOT exists

In contrast to the exists meaning, it is also used in subqueries, taking out conditions that are not satisfied, with a

To make a difference, note that sometimes not exists does not completely replace not in

You can look at the example below and note that the retrieval of not in is all the elements retrieved from an external query that matches the subquery. Match equals returns True, and an element is output. And not exists is not the search principle, he is looking at the number of elements returned if the element returned for not exists is true, the null value is preserved.

You can look at the following examples:

Elvis

2012.12.24

Knowledge sharing ~ Common progress

Reprint Please specify:

http://blog.csdn.net/elvis_dataguru/article/details/8426886