How Should EXSITS be used?

Whether it is a project or SQL, we usually use IN. It is easy to understand. However, IN the face of multi-layer query nesting, or the number of result sets IN the IN keyword is huge, the query efficiency will decrease linearly. In this case, we should make good use of EXSITS.

First, let's give a simple example.

With score (id, name, subject, score) as (select 0, 'zhang san', 'mat', 88 from dual union allselect 1, 'zhang san', 'English ', 78 from dual union allselect 2, 'lily', 'mat', 68 from dual union allselect 3, 'lily', 'inc', 98 from dual union allselect 4, 'wang wu', 'mat', 77 from dual union allselect 5, 'wang wu', 'English ', 92 from dual union allselect 6, 'zhao liu', 'mat ', 81 from dual union allselect 7, 'zhao liu', 'English ', 75 from dual), has_phone (name, has_phone) as (select 'zhang san ', 'Have 'from dual union allselect' Li si', 'No' from dual union allselect' Wang wu', 'No' from dual union allselect' Zhao liu', 'Have' from dual) -- select * -- from score a -- where name in (select name from has_phone where has_phone = 'you') select * from score a where exists (select 1 from has_phone B where B. name =. name and has_phone = 'you ')

This SQL statement queries the scores of students with mobile phones.

Let me understand the difference between IN and EXSITS.
When using IN.
First, the database searches for all the names with "yes" conditions in has_phone.
Then match the result set with each name.

When using EXSITS.
The database first queries the SCORE, and then each connection condition goes to EXSITS for judgment.
If this parameter is set to TRUE, The result set is added. Otherwise, the result set is skipped.

EXSITS Execution Process
It can be understood:
For x in (select * from score)
Loop

If (exists (select 1 from has_phone B where B. name = a. name ))
Then
Output the record;
End if;

End loop;

Performance differences between in and exists:
If the subquery produces fewer results set records, the in statement should be used when the tables in the primary query are large and indexed;
If there are few primary query records in the outer layer, the table in the subquery is large, and there is an index, exists is used.
In fact, we distinguish between in and exists, which mainly results in a change in the driving sequence (this is the key to performance changes). If exists,
The External table is the driver table and accessed first. If the table is IN, the sub-query is executed first, so we will take the fast return of the driver table as the target,
The relationship between the index and the result set will be taken into account.

Original article: http://dacoolbaby.iteye.com/blog/1638990