In, not in, exists, not exists, and not exists are more efficient than Max.

In SQL, there are three types of in:

1. For example, select * from T1 where F1 in ('A', 'B') should be more efficient than the following two

Select * from T1 where F1 = 'A' or F1 = 'B'

Or select * from T1 where F1 = 'A' Union all select * from T1 F1 = 'B'

You may not be referring to this category. We will not discuss it here.

2. For example, select * from T1 where F1 in (select F1 from T2 where t2.fx = 'X '),

The condition in the WHERE clause of the subquery is not affected by the outer query. In general, the automatic optimization is converted into an exist statement, that is, the efficiency is the same as that of the exist statement.

3. For example, select * from T1 where F1 in (select F1 from T2 where t2.fx = t1.fx ),

The condition in the WHERE clause of the subquery is affected by the outer query. The efficiency of such queries depends on the index and data volume of the fields involved in the related conditions,It is generally considered that the efficiency is not as high as exists.

Except for the first in statement, the SQL statements can be converted into exists statements,The general programming habit is to use exists instead of inBut seldom consider the execution efficiency of in and exists.

 

 

Not exists is more efficient than Max.

 

Query the maximum value in the same table.

 

I remember the following tests:

Table

Test

Structure

Id int identity (1, 1), -- id Primary Key \ auto-Increment

Sort int, -- category, each one thousand pieces of data is a category

Sid int -- category ID

Data Records inserted

If you want to query the maximum SID of each category

Select * From test
Where Not exists (select 1 From Test Where Sort = A. sort and Sid > A. Sid)

Ratio

Select * From test
Where Sid In (Select max (SID) from test Where Sort = A. Sort)

The execution efficiency is higher than three times.