Use of exists, not exists, and in Oracle

There are two simple examples of exists, not exists, and in 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. 2) select * from T1 where T1.a in (select T2.a from T2); T1 has a large data volume and T2 data volume is an hour, T1> T2, 2) High query efficiency. Www.2cto.com exists usage: Clause (1) understands its meaning: "select 1 from T2 where T1.a = T2.a" is equivalent to a join Table query, which is equivalent to "select 1 from T1, t2 where T1.a = T2.a "However, If you execute the statement in the brackets (1) separately, a syntax error will be reported, which is also worth attention when using exists. Www.2cto.com "exists (xxx)" indicates whether the statement in the brackets 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 (1) sentence is true. Usage of in: the meaning of the sentence (2): The content of the field searched by the statement in the brackets following "in" must correspond. 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, the method (2) can be used as follows: "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.