Shulou(Shulou.com)05/31 Report--

Today I will tell you what the difference between EXISTS, IN, NOT EXISTS and NOT IN is. The content of the article is good. Now I would like to share it with you. Friends who feel in need can understand it. I hope it will be helpful to you. Let's read it along with the editor's ideas.

The difference between EXISTS, IN, NOT EXISTS and NOT IN:

In is suitable for situations where both the interior and exterior are large, and exists is suitable for situations where the outer result set is very small.

An example of using exists and in

=

Today's market report has a sql and slow, it takes more than 20 minutes to run, as follows:

Update p_container_decl cd

Set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate

Where exists (

Select 1

From (

Select tc.decl_no,tc.goods_no

From p_transfer_cont tc,P_AFFIRM_DO ad

Where tc.GOODS_DECL_NO = ad.DECL_NO

And ad.DECL_NO = 'sssssssssssssssss'

) a

Where a.decl_no = cd.decl_no

And a.goods_no = cd.goods_no

)

The number of records of the three tables mentioned above is not small, all of which are about one million. Based on this situation, I thought of an article by tom I read not long ago, which was about the difference between exists and in.

In refers to the appearance and that table as hash join, while exists refers to the external table as loop. Every time loop queries that table.

In this way, in is suitable for situations where both the interior and exterior are large, and exists is suitable for situations where the outer result set is very small.

In my current situation, it is suitable to use in for query, so I rewrote sql as follows:

Update p_container_decl cd

Set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate

Where (decl_no,goods_no) in

(

Select tc.decl_no,tc.goods_no

From p_transfer_cont tc,P_AFFIRM_DO ad

Where tc.GOODS_DECL_NO = ad.DECL_NO

And ad.DECL_NO = 'ssssssssssss'

)

Let the marketer test, and the result runs within 1 minute. The problem is solved, and it seems that exists and in really have to decide to use it based on the amount of data in the table.

Please note that not in is not logically equivalent to not exists. If you misuse not in, be careful that there is a fatal BUG in your program:

Take a look at the following example:

Create table T1 (C1 number,c2 number)

Create table T2 (C1 number,c2 number)

Insert into T1 values (1Pol 2)

Insert into T1 values (1Pol 3)

Insert into T2 values (1Pol 2)

Insert into T2 values (1 zero null)

Select * from T1 where c2 not in (select c2 from T2)

No rows found

Select * from T1 where not exists (select 1 from T2 where t1.c2=t2.c2)

C1 c2

1 3

As you can see, not in has an unexpected result set with logic errors. If you look at the execution plan of the above two select statements, it will also be different. The latter uses hash_aj.

Therefore, try not to use not in (which invokes subqueries) and try to use not exists (which invokes associated subqueries). If any of the records returned in the subquery contain null values, the query will not return any records, as shown in the example above.

Unless the subquery field has a non-null restriction, you can use not in, and you can also prompt it to connect using hasg_aj or merge_aj.

=

First of all, the moderator (lfree) is the moderator, really insightful.

For In, exists and not in, not exists, in 9i and 10g, if the associated field is not null in the subquery, oracle is treated in basically the same way

In, exists and oracle server will be converted to semi join as much as possible.

Not in, not exists oracle server will be converted to anti join as far as possible

But for not exists, there is a bug when dealing with 9i, that is, 9i does not seem to actively convert to anti join, but it can actively convert not in to anti join.

I think this is the reason for the problem encountered by the landlord.

The difference between not in and not exists caused by Null is mainly caused by null operation.

Select * from T1 where c2 not in (select c2 from T2)

Oracle server needs to operate t1.c2 t2.c2. If null exists in t2.c2, then t1.c2 t2.c2 = = > null, that is, the condition is not satisfied.

So no row selected

-=

Top, especially about not in sub-query with null, has not been noticed before! Remind me ~

The above is the whole content of what is the difference between EXISTS, IN, NOT EXISTS and NOT IN. You can search the previous articles or browse the following articles to learn more about what is the difference between EXISTS, IN, NOT EXISTS and NOT IN. I believe the editor will add more knowledge to you. I hope you can support it!

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.