exists in SQL with in, not exists and not

The system requires SQL optimization to optimize for less efficient SQL, making it more efficient to run, which requires a partial in/not in SQL to be modified to Exists/not exists

Here's how to modify it:

In SQL statement

SELECT ID, category_id, htmlfile, title, CONVERT (varchar), begintime,112) as Pubtime
From Tab_oa_pub WHERE is_check=1 and
category_id in (select ID from tab_oa_pub_cate where no= ' 1 ')
ORDER BY begintime Desc

SQL statement modified to exists
SELECT ID, category_id, htmlfile, title, CONVERT (varchar), begintime,112) as Pubtime
From Tab_oa_pub WHERE is_check=1 and
exists (select ID from tab_oa_pub_cate where Tab_oa_pub.category_id=convert (int,no) and no= ' 1 ')
ORDER BY begintime Desc

Is it really more efficient to analyze exists than in?

Let's talk about in and exists first.
SELECT * from t1 where x in (select y from T2)
In fact, it can be understood as:
SELECT *
from T1, (select distinct y from T2) T2
where t1.x = T2.y;
--If you have a certain SQL optimization experience, from this it is natural to think that T2 must not be a big table, because the T2 need to do a full table of "unique sort", if T2 very large this sort of performance is intolerable. But T1 can be very big, why? The most popular understanding is that T1.X=T2.Y can walk the index. But that's not a good explanation. Imagine that if both t1.x and t2.y have indexes, we know that the index is an orderly structure, so the best solution between T1 and T2 is to take the merge join. In addition, if there is an index on the t2.y, the sorting performance of the T2 is also greatly improved.
SELECT * from t1 where exists (select null from t2 where y = x)
Can be understood as:
For x in (SELECT * from T1)
Loop
if (exists (select null from t2 where y = x.x)
Then
OUTPUT the record!
End If
End Loop
-This is easier to understand, T1 is always a watch scan! Therefore, T1 must not be a large table, and T2 can be very large, because y=x.x can walk T2.Y index.
In combination with the above discussion of in/exists, we can draw a basic general conclusion:In the case of large appearance and small inner table, exists is suitable for small appearance and large inner table.

We should be based on the actual situation to do the corresponding optimization, can not absolutely say who efficiency is low, all things are relative

Original: http://www.cnblogs.com/zwl12549/archive/2007/04/19/720028.html

exists in SQL and in, not exists and not in