mysql not in rewrite query issue

Question

I have a query with NOT IN. I've heard MySQL doesn't support NOT IN, but I can't figure how to rewrite it.

My query is:

SELECT * FROM quests
  WHERE id NOT IN ('" .$quest_completed. "') 
  AND location=" .$location_id. "
  AND (follows=0 OR follows IN ('" .$quest_completed. "')) 
  ORDER BY title

eg:

SELECT * FROM quests 
WHERE id NOT IN (6,21) AND 
  location=8 AND 
  (follows=0 OR follows IN (6,21))
ORDER BY title

Row id 6 is being returned in that when I specified WHERE id NOT IN 6

MySQL does support `NOT IN`: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#function_not-in — Ike Walker, Apr 27 '12 at 00:58
Oh, maybe its my query then. It's returning rows that I dont want it to. — user1022585, Apr 27 '12 at 00:59
I hope those variables have been sanitised. You really ought to use prepared statements as a matter of course. Don't know what I'm talking about? Read about [Bobby Tables](http://bobby-tables.com/) — eggyal, Apr 27 '12 at 01:02
@user1022585: Good to hear (although prepared statements are safer still); nevertheless I'm making the habit of putting up a warning along those lines with every question where I see variables embedded in SQL, for the benefit of anyone else who might come to read it. — eggyal, Apr 27 '12 at 01:07

score 1 · Accepted Answer · answered Apr 27 '12 at 01:04

It looks like, because of your use of apostrophes around the $quest_completed variable, the query that's actually being executed is likely to be NOT IN ('6,21'), rather than NOT IN (6,21) (which I think is what you want?).

The difference is that the first version returns records where id is not equal to the string '6,21', whereas the second version returns records where id is neither the number 6 nor the number 21.

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

MySQL supports NOT IN just fine. A restriction only applies to certain cases, like the one here: MySQL "NOT IN" query

edited May 23 '17 at 12:29

Community

1
1

answered Apr 27 '12 at 00:58

Ry-

218,210
55
464
476

score 0 · Answer 3 · answered Apr 27 '12 at 01:06

0

NOT IN can also be interpreted as logical NOT (some condition) such as

NOT ID in ( 'A', 'B', 'C', 'D' )

SO, if you are having issues as written, just try swapping ...

answered Apr 27 '12 at 01:06

DRapp

47,638
12
72
142

mysql not in rewrite query issue

3 Answers3