SQL "NOT IN" function not working properly

Question

Hi can anyone figure out what's wrong with this sql query. The Not In function is not working properly. in my table the id 1 and 2 should not fetched as they come in subquery in the not in function.

select pr.id
     ,pc.title as category
     ,pc.points_required
     ,pc.total_leads_allowed
     ,pr.leadsSold
     ,pr.created 
  from ad_pro_requests pr 
  join ad_pro_categories pc 
   on pc.id = pr.cat_id 
 where pr.cat_id IN(1,2)
    AND pr.id NOT IN(
        select request_id 
        from ad_purchased_leads 
        where user_id = 8
    ) 
   And pr.leadsSold < pc.total_leads_allowed 
   And pc.active =1 
   And pr.status = 1 
   And pr.placeId = 'CA' 
    OR pr.placeId = 6077243 
ORDER by id desc 
limit 0,15

Answer 1

You - most likely - need to surround the OR condition with parentheses:

where 
    pr.cat_id in (1, 2)
    and pr.id not in (...)
    and ...
    and (pr.placeid = 'CA' or pr.placeid = '6077243')

Or better yet, use in:

where 
    pr.cat_id in (1, 2)
    and pr.id not in (...)
    and ...
    and pr.placeid in ('CA', '6077243')

Note that I surrounded the literal number with single quotes, since placeid seems to be a string.

I would also suggest rewriting the in condition as not exists. It is usually more efficient, and null-safe:

where 
    pr.cat_id in (1, 2)
    and not exists (
        select 1
        from ad_purchased_leads apl
        where apl.user_id = 8 and apl.request_id = pr.id
    )
    and pr.leadsSold < pc.total_leads_allowed 
    and pc.active =1 
    and pr.status = 1
    and pr.placeid in ('CA', '6077243')

For performance, you want an index on ad_purchased_leads(user_id, request_id).

Answer 2

OR has lower precedence than AND. Your WHERE translate to

where (
    pr.cat_id IN(1,2)
        AND pr.id NOT IN(
            select request_id 
            from ad_purchased_leads 
            where user_id = 8
        ) 
       And pr.leadsSold < pc.total_leads_allowed 
       And pc.active =1 
       And pr.status = 1 
       And pr.placeId = 'CA'
) OR (pr.placeId = 6077243)

You probably want

where pr.cat_id IN(1,2)
    AND pr.id NOT IN(
        select request_id 
        from ad_purchased_leads 
        where user_id = 8
    ) 
   And pr.leadsSold < pc.total_leads_allowed 
   And pc.active =1 
   And pr.status = 1 
   And (pr.placeId = 'CA' OR pr.placeId = 6077243)