Negative SELECT in mysql?

[kaptain kernel]

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

select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.C arIndex=BigCata log.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?

[Shawn Wilson]

Re: Negative SELECT in mysql?

kaptain kernel wrote:[color=blue]
>
> How does one retrieve the rows in a select statement that DONT match the
> select.
>
> select CarIndex FROM DealerCatalog, BigCatalog WHERE
> DealerCatalog.C arIndex=BigCata log.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?[/color]

select CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.C arIndex<>BigCat alog.CarIndex

Regards,
Shawn
--
Shawn Wilson
shawn@glassgian t.com

http://www.glassgiant.com

I have a spam filter. Please include "PHP" in the
subject line to ensure I'll get your message.

[Tim Van Wassenhove]

Re: Negative SELECT in mysql?

On 2004-01-13, kaptain kernel <nospam@nospam. gov> wrote:[color=blue]
> How does one retrieve the rows in a select statement that DONT match the
> select.
>
> select CarIndex FROM DealerCatalog, BigCatalog WHERE
> DealerCatalog.C arIndex=BigCata log.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?[/color]

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

[kaptain kernel]

Re: Negative SELECT in mysql?

Tim Van Wassenhove wrote:[color=blue]
> On 2004-01-13, kaptain kernel <nospam@nospam. gov> wrote:
>[color=green]
>>How does one retrieve the rows in a select statement that DONT match the
>>select.
>>
>>select CarIndex FROM DealerCatalog, BigCatalog WHERE
>>DealerCatalog .CarIndex=BigCa talog.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?[/color]
>
>
> 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.
>[/color]


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.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex IS NULL

[Agelmar]

Re: Negative SELECT in mysql?

kaptain kernel wrote:[color=blue]
> Tim Van Wassenhove wrote:[color=green]
>> On 2004-01-13, kaptain kernel <nospam@nospam. gov> wrote:
>>[color=darkred]
>>> How does one retrieve the rows in a select statement that DONT
>>> match the select.
>>>
>>> select CarIndex FROM DealerCatalog, BigCatalog WHERE
>>> DealerCatalog.C arIndex=BigCata log.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?[/color]
>>
>>
>> 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.
>>[/color]
>
>
> 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.[/color]

No kidding. You asked for a cartesian product by specifying a join of two
tables with essentially no join condition...

[color=blue]
> 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.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex
> IS NULL[/color]

Actually, this is definitely not the optimal way to do it, as you are doing
a join but not using any information from the second relation. This is going
to be rather slow. Instead, do:

SELECT CarIndex FROM DealerCatalog WHERE DealerCatalog.C arIndex NOT IN
(SELECT CarIndex FROM BigCatalog);

Additionally, you should make sure that there is an index on
DealerCatalog.C arIndex and also an index on BigCatalog.CarI ndex.

-Ian

[Chung Leong]

Re: Negative SELECT in mysql?

or

SELECT CarIndex FROM DealerCatalog WHERE NOT EXISTS
(SELECT * FROM BigCatalog WHERE DealerCatalog.C arIndex =
BigCatalog.CarI ndex);

Uzytkownik "Agelmar" <ifetteNOSPAM@c omcast.net> napisal w wiadomosci
news:bu1p3b$cim ml$1@ID-30799.news.uni-berlin.de...[color=blue]
> Actually, this is definitely not the optimal way to do it, as you are[/color]
doing[color=blue]
> a join but not using any information from the second relation. This is[/color]
going[color=blue]
> to be rather slow. Instead, do:
>
> SELECT CarIndex FROM DealerCatalog WHERE DealerCatalog.C arIndex NOT IN
> (SELECT CarIndex FROM BigCatalog);
>
> Additionally, you should make sure that there is an index on
> DealerCatalog.C arIndex and also an index on BigCatalog.CarI ndex.
>
> -Ian
>
>[/color]

[Agelmar]

Re: Negative SELECT in mysql?

Chung Leong wrote:[color=blue]
> or
>
> SELECT CarIndex FROM DealerCatalog WHERE NOT EXISTS
> (SELECT * FROM BigCatalog WHERE DealerCatalog.C arIndex =
> BigCatalog.CarI ndex);[/color]

No, that is much slower. Your method will result in a sub-query on the
database for each CarIndex in DealerCatalog. My method results in the
subquery being evaluated only once, as it is not a correlated subquery. The
database retrieves a list of CarIndex tuples from the BigCatalog relation,
and it does this only once, storing this in memory. It then probes this list
for each CarIndex in DealerCatalog. With your method, it takes CarIndex
values from DealerCatalog one at a time, and for each such value, it issues
a query against BigCatalog. Not ideal.

[Jerome H. Gitomer]

Re: Negative SELECT in mysql?

kaptain kernel wrote:[color=blue]
> Tim Van Wassenhove wrote:
>[color=green]
>> On 2004-01-13, kaptain kernel <nospam@nospam. gov> wrote:
>>[color=darkred]
>>> How does one retrieve the rows in a select statement that DONT match the
>>> select.
>>>[/color][/color][/color]
[snip[
[color=blue][color=green][color=darkred]
>>> 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?[/color][/color][/color]

[snip][color=blue]
>
> 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.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex IS
> NULL
>[/color]

If your RDBMS vendor implemented the SET operators using the
MINUS operator may prove to be much faster; e.g.

SELECT CarIndex FROM DealerCatalog
MINUS
SELECT CarIndex FROM BigCatalog
ORDER BY 1;

[Shawn Wilson]

Re: Negative SELECT in mysql?

kaptain kernel wrote:[color=blue]
>
> Tim Van Wassenhove wrote:[color=green]
> > On 2004-01-13, kaptain kernel <nospam@nospam. gov> wrote:
> >[color=darkred]
> >>How does one retrieve the rows in a select statement that DONT match the
> >>select.
> >>
> >>select CarIndex FROM DealerCatalog, BigCatalog WHERE
> >>DealerCatalog .CarIndex=BigCa talog.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?[/color]
> >
> >
> > 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.
> >[/color]
>
> 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.C arIndex=BigCata log.CarIndex WHERE BigCatalog.CarI ndex IS NULL[/color]

My apologies - I didn't notice there 2 tables. I was thinking of just a select
from a single table.

Regards,
Shawn
--
Shawn Wilson
shawn@glassgian t.com

http://www.glassgiant.com

I have a spam filter. Please include "PHP" in the
subject line to ensure I'll get your message.

[Chung Leong]

Re: Negative SELECT in mysql?

Depends on the database software. On MS SQLServer using EXISTS is much
faster than using IN, rather counterintuitiv ely.

Uzytkownik "Agelmar" <ifetteNOSPAM@c omcast.net> napisal w wiadomosci
news:bu28jv$cra lr$1@ID-30799.news.uni-berlin.de...[color=blue]
> Chung Leong wrote:[color=green]
> > or
> >
> > SELECT CarIndex FROM DealerCatalog WHERE NOT EXISTS
> > (SELECT * FROM BigCatalog WHERE DealerCatalog.C arIndex =
> > BigCatalog.CarI ndex);[/color]
>
> No, that is much slower. Your method will result in a sub-query on the
> database for each CarIndex in DealerCatalog. My method results in the
> subquery being evaluated only once, as it is not a correlated subquery.[/color]
The[color=blue]
> database retrieves a list of CarIndex tuples from the BigCatalog relation,
> and it does this only once, storing this in memory. It then probes this[/color]
list[color=blue]
> for each CarIndex in DealerCatalog. With your method, it takes CarIndex
> values from DealerCatalog one at a time, and for each such value, it[/color]
issues[color=blue]
> a query against BigCatalog. Not ideal.
>
>[/color]

[Agelmar]

Re: Negative SELECT in mysql?

Chung Leong wrote:[color=blue]
> Depends on the database software. On MS SQLServer using EXISTS is much
> faster than using IN, rather counterintuitiv ely.[/color]

I wouldn't be suprised. SQL Server has a very powerful, rule-based query
optimizer. MySQL's query optimizer is far less powerful (it's quite minimal
compared to Oracle, DB2, SQL Server...), and so I'm pretty sure that it will
run the IN faster than the NOT EXISTS... but I am too lazy to make a large
data set to actually test it out :-)

[Chung Leong]

Re: Negative SELECT in mysql?

I didn't even know that MySQL can handle subqueries.

Uzytkownik "Agelmar" <ifetteNOSPAM@c omcast.net> napisal w wiadomosci
news:bu4uv6$c5h 57$1@ID-30799.news.uni-berlin.de...[color=blue]
> Chung Leong wrote:[color=green]
> > Depends on the database software. On MS SQLServer using EXISTS is much
> > faster than using IN, rather counterintuitiv ely.[/color]
>
> I wouldn't be suprised. SQL Server has a very powerful, rule-based query
> optimizer. MySQL's query optimizer is far less powerful (it's quite[/color]
minimal[color=blue]
> compared to Oracle, DB2, SQL Server...), and so I'm pretty sure that it[/color]
will[color=blue]
> run the IN faster than the NOT EXISTS... but I am too lazy to make a large
> data set to actually test it out :-)
>
>[/color]

[Dag Sunde]

Re: Negative SELECT in mysql?

Only the latest (or next versions) can...

(I can't remember if it is the current release, or
the beta for the next one).

--
Dag.

"Chung Leong" <chernyshevsky@ hotmail.com> wrote in message
news:LO2dnWosiN Gr95rdRVn-gQ@comcast.com. ..[color=blue]
> I didn't even know that MySQL can handle subqueries.
>
> Uzytkownik "Agelmar" <ifetteNOSPAM@c omcast.net> napisal w wiadomosci
> news:bu4uv6$c5h 57$1@ID-30799.news.uni-berlin.de...[color=green]
> > Chung Leong wrote:[color=darkred]
> > > Depends on the database software. On MS SQLServer using EXISTS is much
> > > faster than using IN, rather counterintuitiv ely.[/color]
> >
> > I wouldn't be suprised. SQL Server has a very powerful, rule-based query
> > optimizer. MySQL's query optimizer is far less powerful (it's quite[/color]
> minimal[color=green]
> > compared to Oracle, DB2, SQL Server...), and so I'm pretty sure that it[/color]
> will[color=green]
> > run the IN faster than the NOT EXISTS... but I am too lazy to make a[/color][/color]
large[color=blue][color=green]
> > data set to actually test it out :-)
> >
> >[/color]
>
>[/color]