Usage of exists and not exists in oracle

There are two simple examples of exists and not exists usage in oracle to illustrate the efficiency of "exists" and "in". 1) select * from T1 where exists (select 1 from T2 where T1.a = T2.a); T1 has a small amount of data, while T2 has a large amount of data. T1 <T2, 1) has a high query efficiency. Www.2cto.com 2) select * from T1 where T1.a in (select T2.a from T2); T1 data volume is very large and T2 data volume is small, T1> T2, 2) the query efficiency is high. Exists usage: Note 1) the section with color fonts in the sentence to understand its meaning. "select 1 from T2 where T1.a = T2.a" is equivalent to a join Table query, it is equivalent to "select 1 from T1, T2 where T1.a = T2.a". However, if you execute the statement in parentheses (1), a syntax error is reported, this is also worth noting when using exists. "Exists (xxx)" indicates whether the statement in parentheses can identify the record and whether the record to be queried exists. Therefore, the "1" in "select 1" is irrelevant. It is okay to replace it with "*". It only cares whether the data in the brackets can be searched out, whether such a record exists. If so, the where condition of the sentence is true. Usage of in: Continue to reference the above example "2) select * from T1 where T1.a in (select T2.a from T2) "Here, the content of the field searched by the statement following the" in "must correspond to each other. Generally, the expression of field a in Table T1 and table T2 should be the same, otherwise it makes no sense to query it. For example, table T1 and table T2 have a field indicating the ticket number. However, table T1 indicates that the ticket number field is named "ticketid" and table T2 indicates "id ", however, the expression is the same, and the data format is the same. In this case, we can use the 2) method as follows: www.2cto.com "select * from T1 where T1.ticketid in (select T2.id from T2) "Select name from employee where name not in (select name from student); Select name from employee where not exists (select name from student ); the first SQL statement is less efficient than the second statement. By using EXISTS, Oracle first checks the primary query, and then runs the subquery until it finds the first match, which saves time. When Oracle executes the IN subquery, it 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 EXISTS is faster than IN queries.