exists and not EXISTS usage in Oracle

from:http://blog.csdn.net/m13666368773/article/details/7007197

exists says () a subquery Returns a result that is not NULL. Indicates that the where condition will execute the main SQL statement, and if NULL indicates that the WHERE condition is not true, the SQL statement will not execute. Instead of exists and exists, the subquery result is empty, which means that the where condition is true and the SQL statement is executed. Responsible for not executing.

Prior to learning Oracle database, contact exists, have done a few simple examples, such as

1. If the department name contains the letter a, query all employee information (using exists)
SELECT * from emp where exists (SELECT * from dept where dname like '%a% ' and deptno = Emp.deptno) Temp and deptno=temp.de Ptno;

The result is:

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH Clerk 7902 1 July-December-80 800 20
7499 ALLEN salesman 7698 20月-February-81 1600 300 30
7521 WARD salesman 7698 2 February-February-81 1250 500 30
7566 JONES MANAGER 7839 February-April-81 2975 20
7654 MARTIN salesman 7698 2 August-September-81 1250 1400 30
7698 BLAKE MANAGER 7839 January-May-81 2850 30
7782 CLARK MANAGER 7839 September-June-81 2450 10
7788 SCOTT ANALYST 7566 1 September-April-87 3000 20
7839 KING President 1 July-November-81 5000 10
7844 TURNER salesman 7698 August-September-81 1500 0 30
7876 ADAMS Clerk 7788 2 March-May-87 1100 20

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7900 JAMES Clerk 7698 March-December-81 950 30
7902 FORD ANALYST 7566 March-December-81 3000 20
7934 MILLER Clerk 7782 2 March-January-82 1300 10

14 rows have been selected.

2. If there is a departmental message with a salary of not less than 1500, then all departmental information is queried (using NOT exists)
SELECT * FROM dept where isn't exists (select Deptno from emp where deptno = Emp.deptno GROUP BY DEPTNO have avg (SAL) < Exists (SELECT * from emp where emp.deptno = Deptno);

Detailed usage of exists reprint http://05rjyzl11.iteye.com/blog/699673

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