Usage of exists and not exists in Oracle

 

Exists indicates that the results returned by the subquery statement in () are not null, indicating that the where condition is true, the primary SQL statement is executed. If it is null, the where condition is invalid, and the SQL statement is not executed. Not exists is opposite to exists. If the subquery result is null, the where condition is true and the SQL statement is executed. Not executed.

I used to contact exists when I was learning Oracle databases. I have made several simple examples, such

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. deptno;

Result:

Empno ename job Mgr hiredate Sal comm deptno
-----------------------------------------------------------------------------------
7369 Smith clerk 7902-12-80 800 20
7499 Allen salesman 7698 20-2 month-81 1600 300 30
7521 ward salesman 7698 22-2 month-81 1250 500 30
7566 Jones manager 7839 2975-81 20
7654 Martin salesman 7698 28-9 month-81 1250 1400 30
7698 Blake manager 7839 01-5 months-81 2850 30
7782 Clark manager 7839-81 2450 10
7788 Scott analyst 7566-87 3000 20
7839 King President 17-11 month-81 5000 10
7844 Turner salesman 7698 month-81 1500 0 30
7876 Adams clerk 7788 month-87 1100 20

Empno ename job Mgr hiredate Sal comm deptno
-----------------------------------------------------------------------------------
7900 James Clerk 7698 03-12 months-81 950 30
7902 Ford analyst 7566 03-12 months-81 3000 20
7934 Miller clerk 7782 23-1 month-82 1300 10

14 rows have been selected.

2. If the average salary is not less than 1500 of the Department Information, query all Department Information (use not exists)
Select * from Dept where not exists (select deptno from EMP where deptno = EMP. deptno group by deptno having AVG (SAL) <1500) and exists (select * from EMP where EMP. deptno = deptno );

 

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

 

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

1) Select * from T1 where exists (select 1 from T2 where t1.a = t2.a );

   Small Data size of T1 and large data size of T2, t1 <t2, 1) high query efficiency.

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

    T1 has a large data volume and T2 has an hour, T1> T2, 2) High query efficiency.

Exists usage:

Note: 1) the section in the sentence contains a color font to understand its meaning;

"Select 1 from T2 where t1.a = t2.a" is equivalent to a join Table query, which is equivalent

"Select 1 from T1, T2   Where t1.a = t2.a"

However, if you execute the statement in parentheses (1), a syntax error will be reported, which is also worth attention 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 )"

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 must be the same, otherwise, this query is meaningless.

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, use the 2) method 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.