mysql否定,mysql中的否定SELECT？

How does one retrieve the rows in a select statement that DONT match the

select.

select CarIndex FROM DealerCatalog, BigCatalog WHERE

DealerCatalog.CarIndex=BigCatalog.CarIndex

finds all the cars in the dealer catalog that are in the bigger distributor

catalog.

How do I do the opposite in a single sql statement i.e. all the dealer cars

that AREN''T in the big distributor catalog?

Is there a negative Select?

解决方案kaptain kernel wrote:How does one retrieve the rows in a select statement that DONT match the

select.

select CarIndex FROM DealerCatalog, BigCatalog WHERE

DealerCatalog.CarIndex=BigCatalog.CarIndex

finds all the cars in the dealer catalog that are in the bigger distributor

catalog.

How do I do the opposite in a single sql statement i.e. all the dealer cars

that AREN''T in the big distributor catalog?

Is there a negative Select?

select CarIndex FROM DealerCatalog, BigCatalog WHERE

DealerCatalog.CarIndex<>BigCatalog.CarIndex

Regards,

Shawn

--

Shawn Wilson

sh***@glassgiant.com

http://www.glassgiant.com

I have a spam filter. Please include "PHP" in the

subject line to ensure I''ll get your message.

On 2004-01-13, kaptain kernel wrote:How does one retrieve the rows in a select statement that DONT match the

select.

select CarIndex FROM DealerCatalog, BigCatalog WHERE

DealerCatalog.CarIndex=BigCatalog.CarIndex

finds all the cars in the dealer catalog that are in the bigger distributor

catalog.

How do I do the opposite in a single sql statement i.e. all the dealer cars

that AREN''T in the big distributor catalog?

Is there a negative Select?

Lookup the section about the WHERE clause in your database manual.

In this case it''s enough to reverse the condition.

More general you might use the not in with subquery construct.

--

http://home.mysth.be/~timvw

Tim Van Wassenhove wrote:On 2004-01-13, kaptain kernel wrote:

How does one retrieve the rows in a select statement that DONT match the

select.

select CarIndex FROM DealerCatalog, BigCatalog WHERE

DealerCatalog.CarIndex=BigCatalog.CarIndex

finds all the cars in the dealer catalog that are in the bigger distributor

catalog.

How do I do the opposite in a single sql statement i.e. all the dealer cars

that AREN''T in the big distributor catalog?

Is there a negative Select?

Lookup the section about the WHERE clause in your database manual.

In this case it''s enough to reverse the condition.

More general you might use the not in with subquery construct.

The suggestions mentioned above are certainly not speedy - I had to kill

a sql query when i changed the = in my original statement to <> as

suggested above. Which tells me that the suggestions are wrong.

The answer is to use LEFT JOIN - anything that doesn''t join is given a

NULL value , and it''s a heck of a lot speedier of large datasets (i''ve

got 12,000 records):

SELECT CarIndex FROM DealerCatalog LEFT JOIN BigCatalog ON

DealerCatalog.CarIndex=BigCatalog.CarIndex WHERE BigCatalog.CarIndex IS NULL