0

I want to find all codes from promo_id = 1 that does not exist in promo_id = 2

Table name: promo_codes

Columns: id, promo_id, code

SELECT code FROM promo_codes where promo_id=1 NOT IN

(SELECT code FROM promo_codes where promo_id=2);

I always get 0 row(s) returned, even though there are codes in promo_id = 1 that does not exist in promo_id = 2

6

You want to check in code is NOT IN that list of codes. Your syntax needs correction. Specifically, to use AND code NOT IN (...):

SELECT code 
FROM promo_codes 
WHERE promo_id = 1 
  AND code NOT IN
      (SELECT code FROM promo_codes WHERE promo_id = 2) ;

If code is nullable, you should add one more check in the subquery to avoid NULL issues with NOT IN:

SELECT code 
FROM promo_codes 
WHERE promo_id = 1 
  AND code NOT IN
      (SELECT code
       FROM promo_codes
       WHERE promo_id = 2
         AND code IS NOT NULL) ;

Alternatively, you can use NOT EXISTS or LEFT JOIN / IS NULL check. They are often more efficient than NOT IN (subquery), especially in older MySQL versions:

-- using NOT EXISTS
SELECT p1.code 
FROM promo_codes AS p1 
WHERE p1.promo_id = 1 
  AND NOT EXISTS
      (SELECT 1
       FROM promo_codes AS p2
       WHERE p2.promo_id = 2
         AND p2.code = p1.code) ;

-- using LEFT JOIN / IS NULL
SELECT p1.code 
FROM promo_codes AS p1 
     LEFT JOIN promo_codes AS p2
     ON  p2.promo_id = 2
     AND p2.code = p1.code
WHERE p1.promo_id = 1 
  AND p2.promo_id IS NULL ;

For efficiency, I suggest you add a composite index on (promo_id, code), independent of which query you choose (and test them all to find out which runs faster in your case)

0

NOT IN (<Select .. subquery>) is generally inefficient in MySQL. You can also approach this problem using conditional aggregation based filtering using Group By and Having clauses:

Select code 
From promo_codes 
Where promo_id IN (1,2) 
Group By code 
Having Sum(promo_id = 1)
              And Not Sum(promo_id = 2)
  • 1
    Good but I would write it as: Having Sum(promo_id = 1) > 0 And Sum(promo_id = 2) = 0, so it is more clear. Or: Having COUNT(CASE WHEN promo_id = 1 THEN 1 END) > 0 AND COUNT(CASE WHEN promo_id = 2 THEN 1 END) = 0 – ypercubeᵀᴹ Sep 8 at 10:22
0

Is this what you want. The below query will run row by row and each code of promo_id 1 will be checked in (codes...) of promo_id 2 if theres a match then that row would be printed

    Select codes from table where 
      promo_id 
     =1 and codes NOT IN
     (SELECT CODES from table where 
      promo_code=2) 
0

If the not-in-list contains any nulls, then not-in always returns null. So you get no rows.

SELECT code FROM promo_codes where promo_id=1 and code NOT IN
(SELECT code FROM promo_codes where promo_id=2 and code is not null);

You could also use "not exists", or "except".

  • 1
    That might be a problem, but only after they fix the syntax (see my answer ;) – ypercubeᵀᴹ Sep 8 at 10:09
  • 1
    You have two WHEREs! – Vérace Sep 8 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.