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

This article focuses on "how to replace the Not In method in the sql sentence", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to replace the Not In method in the sql statement.

Objective:

Replace the NOT IN method.

Description:

In a single SQL statement, no stored procedures or temporary tables are used. Using stored procedures and temporary tables is beyond the scope of this article.

Achieve:

Example:

Table aa: structure

Id value...

1 a

2 b

3 c

4 d

5 e

6 f

-

Table bb: structure

Id...

two

four

six

Now I'm going to take all the fields in the table aa, provided that the id value of aa is not in the id value of bb (not in). That is, all fields with odd id should be returned.

SQL using NOT IN:

Select * from aa where id not in (select id from bb)

Just one sentence, simple and clear, but it is a pity that the efficiency is not high, and the company specification requires that NOT IN should not be used as far as possible, which cost me a lot of crying.

Modified SQL:

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

Explain. At the beginning, I first wanted to use inline tables, but every time I found a good way, I just couldn't achieve it. There is a good way to teach first. )

Later, I considered the logic of NOT IN. A NOT IN B means that An is the main body, and B only plays a judgment role. We can first query all qualified A records regardless of whether they belong to B or not, and then remove the value that also belongs to B from here.

Select aa.* from aa

But this is not enough. We cannot use the returned result set to determine whether it belongs to B or not and exclude it. For this reason, I want to construct a temporary column whose value should be within the range of A's result set. All values in B. The main body of this result set should be all the A that satisfies the prerequisite, and then add the B that meets the condition, while the B value that does not meet the condition is no longer considered, so left join is used.

This paragraph is the key. I don't know if I have explained it clearly. Continue to read if I don't understand.

So this sentence came out.

Select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id

If you don't understand the above, you will understand if you look at the result set.

Id value tempcolum

-

1 a NULL

2 b 2

3 c NULL

4 d 4

5 e NULL

6 f 6

Seeing this result set, I think everyone knows what I mean. By the way, we are going to do secondary operations on this result set.

As you can see, the resulting result set contains all the qualified table AA fields and the id of bb. If the value in aa is in bb, then the value of tempcolum will not be null, if it is not, it will be null, so we only need to query all tempcolumn values of null from this result set to meet our requirements.

So the final sql 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, and we don't want the rest.

Result

Id value

-

1 a

3 c

5 e

Ok, realized, hope to be helpful to all of you.

At this point, I believe you have a deeper understanding of "how to replace the Not In method in the sql statement". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.