In and not of multiple fields and their alternate notation (Exists,not exists)

First of all declare: Database master Please go away, you saw an eyesore ah. <?xml:namespace prefix = o ns = "Urn:schemas-microsoft-com:office:office"/>

Sometimes when the query, Ah, a field in, and not in simply can not meet the requirements, very much like more than one field in the, not in, but many databases do not support more than one field in the, not in (DB2 is supported), it is estimated that many friends are depressed bar! But it doesn't matter, I do not write the article, hehe.

I used the database has Access,sql server,db2, laughed at AH. In fact also on the SQL SERVER,DB2 these two also like Dot, hehe ...

First declare that DB2 is supported by multiple fields in, not in

first of all, the basics:

Database: DB2 8.2,sql Server Express

Table A has fields: AAA,BBB, and there may be other fields. Number of records: 3764

Table B has fields: AAA,BBB, and there may be other fields. Number of records: 4127

It's obvious, that's the field of Table A. AAA has a corresponding relationship with the field of Table B, which is the BBB of the table a field and the field BBB of Table B.

But only aaa,bbb two fields are matched on the same time is really the corresponding. (also don't know I said clear, understand long live AH)

Okay, start text:

1. say "in" first.

From table B, query for a record that satisfies the condition "select aaa,bbb from":

The following statement is the result we want:

SELECT * from B where (AAA,BBB) in (select aaa,bbb from a);

Unfortunately, the above statement can only be executed on DB2, SQL Server is not. (other databases have not tried, do not know AH.) ）

Fortunately, you can use the following statement instead

SELECT * from B where exists (SELECT * from a where a.aaa=b.aaa and A.BBB=B.BBB);

Of course, you might say my condition is "select aaa,bbb from a where table a" a field 1= ' ... ' and table a a field 2><?xml:namespace prefix = st1 ns = "URN:SCHEMAS-MICR Osoft-com:office:smarttags "/>1111" and so on, I'll take the "query condition a" represents

That is: query condition A = Table A a field 1= ' ... ' and table a a field 2>1111

That's the way it should be written.

SELECT * from B where (AAA,BBB) in (select AAA,BBB from a where query condition a);

SELECT * from B where exists (SELECT * from a where a.aaa=b.aaa and a.bbb=b.bbb and query condition a);

When using exists, it is best to write "Table a field 1" in "query condition a" as "a. Table A field 1". Reason to think of AH.

2. say "not in". basic and "in", I just copied it, and stole a lazy

Query from table B for records that are not in the result set "select Aaa,bbb from":

The following statement is the result we want:

SELECT * from B where (AAA,BBB) isn't in (select aaa,bbb from a);

Unfortunately, the above statement can only be executed on DB2, SQL Server is not. (other databases have not tried, do not know AH.) ）

Fortunately, you can use the following statement instead

SELECT * from B where NOT exists (SELECT * from a where a.aaa=b.aaa and A.BBB=B.BBB);

Of course, you might say my condition is "select aaa,bbb from a where table a" a field 1= ' ... ' and table a a field 2>1111 "What, wait, I'll use" query condition a "to represent

That is: query condition A = Table A a field 1= ' ... ' and table a a field 2>1111

That's the way it should be written.

SELECT * from B where (AAA,BBB) isn't in (select aaa,bbb from a where query condition a);

SELECT * from B where NOT exists (SELECT * from a where a.aaa=b.aaa and a.bbb=b.bbb and query condition a);

When using not exists, it is best to write "Table a field 1" in "query condition a" as "a. Table A field 1". Reason to think of AH.

OK, said, the following on a few aspects of comparison bar (although the significance is not very big, hehe)

in writing:

Of course in, not in the most intuitive (earth people know).

talk about the efficiency problem ( DB2 only, why not say it) :

In efficiency is higher than exists

Not exists more efficient than not in high

The specific execution time is as follows

In 0.01 secs

exists 0.03 secs

Not in 8.62 secs

Not exists 0.03 secs

Summary:

Multi-field in and not in can be performed in DB2 data, and SQL Server is not. (other databases have not tried, do not know.) ）

exists, not exists can be performed in Db2,sql server. (other databases have not tried, do not know.) ）

And the overall use of exists, not exists efficiency is very high, we suggest that we still use good exists, not exists it.