[Today's technology learning] What should I do if not in SQL Server is poor?

Sqlserver will not give you data results when using a large amount of data for a not in comparison.

For example:Select a. * from a where a. id not in (select ID from B where .....)

We all know that we recommend that you do not use the not in method in many SQL articles, because the efficiency of this method is not high. Is there any alternative? (Declared that the stored procedure could not be used because of the current situation, so only SQL statements can be written.) After experiment with colleagues, the results were implemented using the following methods.

 

Purpose:

Replace the not in method.

Note:

In a single SQL statement, no stored procedures or temporary tables are used. Using Stored Procedures and temporary tables is not covered in this article.

Implementation:

Example:

Table AA: Structure

ID value ......

1

2 B

3 C

4 d

5 e

6 F

Bytes ---------------------------------------------------------------------------------------------

 

Table BB:Structure

Id ......

2

4

6

 

Now I want to retrieve all fields in Table AA. The condition is that the id value of AA is not in the ID value of BB ). That is, all fields with an odd ID are returned.

 

Use not inSQL:

Select * from AA where id not in (select ID from BB)

Just one statement is simple and clear, but it is a pity that the efficiency is not high, and the company's regulatory requirements do not need not in, it cost me a lot of money crying ......

 

SQL statement after transformation:

Select CC. ID, CC. value from (select AA. *, BB. ID as tempcolum from AA left join BB on AA. id = BB. ID) as CC where CC. tempcolum is null

 

Let's explain it. At the beginning, I first wanted to use the inline table method, but every time I found a good solution, I simply couldn't implement it. (You Have A Good Idea .)

Later, I thought about the logic of not in. A not in B means that A is the subject, and B plays only a judgment role, we can first query all the qualified a records, regardless of whether the record belongs to B, and then remove the part where the value belongs to B at the same time.

Select AA. * from AA

However, this is not enough. We cannot use this returned result set to determine whether it belongs to B and exclude it. Therefore, I want to construct a temporary column,The value of this column should be inAWithin the result set range.The subject of this result set should be all a that meets the prerequisites, and then add B that meets the conditions, while B that does not meet the conditions will not take into account the scope, therefore, left join is used.

This section is the key. I don't know if I have explained it clearly.

That's why this sentence came out.

Select AA. *, BB. ID as tempcolum from AA left join BB on AA. ID = BB. ID

You can understand the result set without looking at it.

ID value tempcolum

---------------------------------------------

1 A null

2 B 2

3 C null

4 D 4

5 e null

6 F 6

 

I think everyone understands what I mean when I see this result set. By the way, we need to perform a secondary operation on this result set.

As you can see, the generated result set contains the AA field and bb id of all qualified tables. If the value of AA is in BB, then the value of tempcolum is not null. If it is not null, we only need to query all the results set whose tempcolum value is null to meet our requirements.

So the final SQL statement came out.

Select CC. ID, CC. value from (select AA. *, BB. ID as tempcolum from AA left join BB on AA. id = BB. ID) as CC where CC. tempcolum is null

We only need two fields: ID and value. We don't need any other fields.

Result

ID value

-------------------------

1

3 C

5 e