Shulou(Shulou.com)06/01 Report--

The editor believes that everyone knows the difference between in and exists:

1. The application is different.

In in sql is suitable for tables with fewer result sets in a subquery and larger and indexed tables in the main query. Exist in sql is suitable for the outer layer when the main query records are few, the table in the subquery is large, and there is an index.

2. Different driving sequence

IN first queries the table of the subquery, then makes a Cartesian product of the inner table and the outer table, and then filters according to the criteria. Exists is driven by the outer table and is accessed first.

3. The underlying principles are different.

In connects the outer table with the inner table as hash, while exists uses the external table as a loop loop, and each loop loop queries the inner table.

But I saw a blogger yesterday say that another difference is that null values are handled differently: IN does not handle NULL, exists handles null values. I haven't heard of this, so I tested it out of curiosity. I found that there was no difference, and the data from the query was the same. As for the other editors, they will not be tested here one by one, but those who are interested will test them in private.

First create two tables stu to store the student's number, name and class

The grade table stores the student's number and score.

The code is as follows:

Select from stu T1 where exists (select from grade T2 where t1.stuno = t2.stuno) order by t1.stuno

Select from stu T1 where t1.stuno in (select t2.stuno from grade T2 where t1.stuno = t2.stuno) order by t1.stuno

The query result is the same, as follows:

Pay attention to two points here:

1. The fields queried in sql are all fields in the stu table and cannot be written as:

Select t2. From stu T1 where t1.stuno in (select t2.stuno from grade T2 where t1.stuno = t2.stuno) order by T1. The identifier is not valid.

2. In is a single-field query, so we must pay attention to it in the subquery. Cannot be written as:

Select from stu T1 where t1.stuno in (select * from grade T2 where t1.stuno = t2.stuno) order by t1.initio; otherwise too many error values will be reported.

If any friends have different opinions, you are welcome to leave a message for the editor and correct it in time. Thank you!

Next, let's discuss exists and distinct deweighting:

The code is as follows, and id is unique:

Select from stu T1 where exists (select from grade T2 where t1.stuno = t2.stuno) order by t1.stuno

The result of the query is as follows:

I have been bothered here at that time. Doesn't exists have the effect of removing weight? Why does the id still have duplicate data in the data? later, the editor has been doing experimental tests and found that the queried data seems to first meet the where conditions in the subquery, and then deduplicates the data in the subquery, not the main table, and finally returns the data in the main table that matches the data. Later, an additional test was done based on this discovery, and it was found that this was true.

You can also replace it with sql code:

Select T1. * from stu T1, (select distinct a.stuno from grade a) T2 where t1.stuno = t2.stuno order by t1.stuno

I believe that you can not directly achieve the effect of exists query through distinct through these two codes! Exists will be more efficient:

Screenshots of the explanation plan window for the following exists:

Screenshots of the explanation plan window for the following distinct:

So the editor tidies up today and hopes that more netizens will not be stupid like me again, waste time not to say, and have a wrong understanding all the time!

Do you have any better ideas? welcome to leave a message for the editor!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.