exists and not EXISTS usage in Oracle

There are two simple examples to illustrate the efficiency issues of "exists" and "in" 1) SELECT * from T1 where exists (select 1 from T2 where t1.a=t2.a); When the T1 data volume is small and the T2 data is very large, the query efficiency of T1<<T2 1) is high. 2) SELECT * from T1 where t1.a in (select t2.a from T2); T1 data volume is very large and T2 data amount of hours, T1>>T2, 2) query efficiency is high. Exists usage: Please note 1) the part of the sentence that has the color font to understand its meaning; Where "Select 1 from T2 where t1.a=t2.a" is equivalent to an association table query, which is equivalent to "Select 1 from t1,t2 where t1.a=t2.a" However, if you are executing a 1 sentence in parentheses, you will report a syntax error, which is also where you need to be aware of using exists. "Exists (XXX)" means that the parentheses in the sentence can be found in the record, it is to check whether the record exists. So "SELECT 1" Here "1" is actually irrelevant, instead of "*" is not a problem, it only care about the data in parentheses can find out, whether there is such a record, if there is, this 1) sentence where the condition is established. In usage: Continue to cite the example above "2) SELECT * from T1 where t1.a in (select t2.a from T2)" Here the "in" after the sentence in parentheses to search out the contents of the field must correspond to, in general, T1 and T2 the table of the A field expression should be the same meaning, otherwise it makes no sense. For example: T1,t2 table has a field, indicating the work order number, but T1 that the field named "Ticketid", T2 is "id", but the meaning of the expression is the same, and the data format is the same. At this point, you can do this with 2): "SELECT * from T1 where T1.ticketid in (select T2.id from T2)" Select name from employee where name is not in (select name from student); Select name from the employee where NOT exists (select name from student); The first sentence of SQL statement is less efficient than the second sentence. By using Exists,oracle, you first check the main query and then run the subquery until it finds the first match, which saves time. When Oracle executes an in subquery, it executes the subquery first and stores the resulting list in an indexed temporary table. Before executing a subquery, the system first suspends the primary query, and executes the subquery until it has been executed, and then performs the main query after the query is held in the temporary table. This is why using exists is faster than using in usually queries

exists and not EXISTS usage in Oracle