Subject | Re: [firebird-support] Covert not in to not exists |
---|---|

Author | Svein Erling Tysvaer |

Post date | 2006-08-03T14:16:02Z |

Well, yes - if you do a LEFT JOIN and check for NULL I think you can do

this to get the same result:

select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),

sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG

LEFT JOIN TURNOVER_PC_SUBGROUP ON TO2CSGPID3=49 and TO2PCID = BLPCID

where BLCSGPID3=49

and BLPCID is not null

and TO2PCID is null

group by BLPCID

Though test that it gets the right result, I always prefer to use NOT

EXISTS myself (just because I'm accustomed to it, I don't know which of

the two is faster or better from a design point).

Set

Guido Klapperich wrote:

this to get the same result:

select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),

sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG

LEFT JOIN TURNOVER_PC_SUBGROUP ON TO2CSGPID3=49 and TO2PCID = BLPCID

where BLCSGPID3=49

and BLPCID is not null

and TO2PCID is null

group by BLPCID

Though test that it gets the right result, I always prefer to use NOT

EXISTS myself (just because I'm accustomed to it, I don't know which of

the two is faster or better from a design point).

Set

Guido Klapperich wrote:

> Thanks Svein,

>

> is it possible to get the same result with a join?

>

> Regards

>

> Guido

>

> Svein Erling Tysvaer wrote:

>> That's simple Guido, just move TO2PCID and BLPCID into the where clause!

>>

>> select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),

>> sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG

>> where BLCSGPID3=49 and

>> not exists(select * from TURNOVER_PC_SUBGROUP where

>> TO2CSGPID3=49 and TO2PCID = BLPCID)

>> and BLPCID is not null

>> group by BLPCID

>>

>> I added the 'and BLOPCID is not null' because I think the handling of

>> NULL values may be slightly different between NOT IN and NOT EXISTS.

>>

>> Set

>>

>> Guido Klapperich wrote:

>>> I have the following query

>>>

>>> select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),

>>> sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG

>>> where BLCSGPID3=49 and

>>> BLPCID not in (select TO2PCID from TURNOVER_PC_SUBGROUP where

>>> TO2CSGPID3=49)

>>> group by BLPCID

>>>

>>> I have read in the malinglist, that FB 2.0 don't use indexes with 'not

>>> in' any more. Therefore I want to use 'not exists', but I'm not able to

>>> convert the query. Can anybody help me?

>>>

>>> Regards

>>>

>>> Guido