weird postgresql query

Question

I could not make sense of a query, I found a workaround to get the data but I would like to know what happened.

so basically I want to obtain all ids that are not contained in another table. if I count them separately I get this:

select count(distinct product_sid) 
from gaps_inp.pim_product_channel pc 
where pc.channel_code = 'Amazon'

count

200658

and then count items on the other table:

select count(w.sid)
from gaps_fend.product_whitelist w 
where w.channel_code  = 'Amazon'

count

39697

but now if I try to count the difference:

select count(*) 
from gaps_inp.pim_product_channel pc 
where pc.channel_code = 'Amazon' 
and pc.product_sid not in (
  select w.sid
  from gaps_fend.product_whitelist w 
  where w.channel_code  = 'Amazon'
);

count

0

both fields gaps_inp.pim_product_channel.product_sid and gaps_fend.product_whitelist.sid are bigint

I was able to do it by using a left join and a where sid is null, but still I would like to know what I did wrong in the where not in query.

this is the workaround:

select count(distinct pc.product_sid)
from gaps_inp.pim_product_channel pc 
left join gaps_fend.product_whitelist w on w.channel_code = 'Amazon' and pc.product_sid = w.sid
where pc.channel_code = 'Amazon'
and w.sid is null;

count

160968

Pரதீப் · Accepted Answer · 2016-12-06 14:27:44Z

2

Am sure there are some NULL values present in below query

select w.sid
from gaps_fend.product_whitelist w 
where w.channel_code  = 'Amazon'

NOT IN fails when the sub-query returns any NULL values so you are getting zero count. So the work around is using LEFT JOIN or NOT EXISTS or adding IS NOT NULL condition in sub-query

NOT EXISTS method which can handle NULL values

SELECT Count(*)
FROM   gaps_inp.pim_product_channel pc
WHERE  pc.channel_code = 'Amazon'
       AND NOT EXISTS (SELECT 1
                       FROM   gaps_fend.product_whitelist w
                       WHERE  w.channel_code = 'Amazon'
                              AND w.sid = pc.product_sid);

edited Dec 6, 2016 at 14:27

answered Dec 6, 2016 at 14:21

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

I missed your answer when I answered.

David דודו Markovitz · Accepted Answer · 2016-12-06 14:32:45Z

0

not in returns no rows if there is a NULL value returned by the sub-query.
Add and w.sid is not null

edited Dec 6, 2016 at 14:32

answered Dec 6, 2016 at 14:22

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Collectives™ on Stack Overflow

weird postgresql query

count

count

count

count

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

count

count

count

count

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related