oracle in exists, and in brief usage description

Nov 20 posted in Oracle (RSS), 77 views.

oracle in exists, and in brief usage description

There are two simple examples to illustrate the "exists" and the "in" the efficiency of the

1) select * from T1 where exists (select 1 from T2 where T1. A = T2. A);

T1 and T2 a small amount of data very large amounts of data, T1 <<T2, when, 1) the query efficiency.

2) select * from T1 where T1.a in (select T2.a from T2);

T1 and T2 the amount of data very large amount of data an hour, T1>> T2, when, 2) the query efficiency.

exists Usage:

Please note that 1) sentence, part of colored fonts, to understand its meaning;

One "select 1 from T2 where T1. A = T2. A" is equivalent to an association table query, which is equivalent

"Select 1 from T1, T2 where T1. A = T2. A"

However, if you Dangdang the implementation of a) sentence of the statement in brackets, it will report a syntax error, which exists also used to note areas.

"Exists (xxx)" says that the statement in brackets can detect record, it is to check the record exists.

Therefore, "select 1" where "a" is actually irrelevant, replaced by "*" It's no problem, it only cares about the data in brackets can find out whether there is such a record, if it exists, this one) sentence The where condition is.

in the usage:

To continue the example cited above,

"2) select * from T1 where T1.a in (select T2.a from T2)"

Here "in" in parentheses after the statement search out the contents of the field must correspond, in general, T1 and T2 the two tables of a field of significance of the expression should be the same, otherwise, check no sense that .

An analogy: T1, T2 table has a field, said the work order number, but the T1 indicated a single issue of the field work called "ticketid", T2 was "id", but the significance of the expression is the same, and the data format is the same. At this time, with 2) can be written like this:

"Select * from T1 where T1.ticketid in (select T2.id from T2)"

Of course, exists, and in other usage, do not be introduced here,