How can I get the opposite of WHERE id IN (...)?

Question

The following query looks the table ids and return the id if for example 204720966837 is found.
My question is how can I do the opposite? I want to display the ids of those id that are not found.

SELECT id FROM ids WHERE id IN  ('204720966837', '163700377960', '24583115900', '1081617368').

For now I have 204720966837 saved in ids and this is what it prints. My goal is to print 163700377960, 24583115900, 1081617368

while($assoc = mysql_fetch_assoc($query)) {
    echo $assoc['id'];   
}

I have tried NOT IN but I get a zero return.

NOT IN sounds right to me. Are you sure that zero return isn't the correct answer for your data? Really sure? — JohnFx, Mar 27 '12 at 00:16
@JohnFx How can it be right when it does not return me 163700377960, 24583115900, 1081617368? I have only 204720966837 saved in. — EnexoOnoma, Mar 27 '12 at 00:17
Break the problem into 2 steps - what happens if you run the `NOT IN` query directly against your db (MySQL WorkBench/phpMyAdmin)? I can't see any reason `NOT IN` wouldn't work if the data's there... — Basic, Mar 27 '12 at 00:19
@Basic I run it through phpMyAdmin. When I use `IN` it returns me the one it is saved. When I use `NOT IN` I get 0 rows. — EnexoOnoma, Mar 27 '12 at 00:20
Select count(*) from ids; then select count(*) from ids where ID in ('your list') then select count(*) from ides where ID not in ('your list') if you Not in + in is <> count then you have a serious problem. as your db doesn't understand set logic. — xQbert, Mar 27 '12 at 01:01

Basic · Accepted Answer · 2012-03-27T00:31:52.167

4

You could use a temporary table:

CREATE TEMPORARY TABLE temp_table (id int);

INSERT INTO temp_table(id) VALUES('204720966837'), ('163700377960'), ('24583115900'), ('1081617368');

SELECT id FROM temp_table WHERE id NOT IN(SELECT id FROM ids)

DROP TABLE temp_table;

edited Mar 27 '12 at 00:31

answered Mar 27 '12 at 00:14

Basic

26,321
24
115
201

He's not looking for the rows in the database that aren't in his list of IDs - he's looking for the IDs in his list that aren't in the database. – Sam Dufel Mar 27 '12 at 00:26

score 3 · Answer 2 · answered Mar 27 '12 at 00:21

3

My wild guess is that you want:

SELECT id
FROM
  ( SELECT 204720966837 AS id
      UNION ALL
    SELECT 163700377960
      UNION ALL
    SELECT 24583115900
      UNION ALL
    SELECT 1081617368 
  ) AS tmp
WHERE id NOT IN
      ( SELECT id FROM ids )

answered Mar 27 '12 at 00:21

ypercubeᵀᴹ

113,259
19
174
235

1

@Basic: "gah" stands for [awesome](http://dilbert.com/strips/comic/2011-12-30/), I suppose :) – ypercubeᵀᴹ Mar 27 '12 at 00:32
I'd go as far as functional and readable, however, it lacks a little elegance :) – Basic Mar 27 '12 at 00:34
@Basic: Unfortunately MySQL does not have the elegant [Table Value Constructors](http://www.simple-talk.com/sql/sql-training/table-value-constructors-in-sql-server-2008/) – ypercubeᵀᴹ Mar 27 '12 at 00:36
@ypercube couldn't agree more - I cheat further and use the Entity Framework now - I never even touch SQL during development :) – Basic Mar 27 '12 at 00:38
Another - though similar - way: `SELECT tmp.id FROM ids RIGHT JOIN (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3) AS tmp ON tmp.id=ids.id WHERE ids.id IS NULL` I still wonder why there doesn't seem to be a shorter way to write a result set, without all these `UNION`... – Stock Overflaw Mar 27 '12 at 00:38
@StockOverflaw: There is, it's called Table Value Constructor. AFAIK only Postgres and lately SQL-Server support that. – ypercubeᵀᴹ Mar 27 '12 at 00:45
@ypercube ah right I missed the linked article you posted, thanks for the (repeated) info ;) – Stock Overflaw Mar 27 '12 at 01:08

score 2 · Answer 3 · edited May 23 '17 at 10:08

2

NOT IN only works if you have a set returned.

try

SELECT id FROM ids WHERE id NOT IN (SELECT '204720966837', '163700377960', '24583115900', '1081617368')

See MySQL "NOT IN" query for a comprehensive discussion on this topic.

edited May 23 '17 at 10:08

Community

1
1

answered Mar 27 '12 at 00:17

hkf

4,440
1
30
44

#1241 - Operand should contain 1 column(s) – EnexoOnoma Mar 27 '12 at 00:19
1

A list of Ids is treated as a set - The OP's problem in the linked Q is that he's simply referring to `table2.principal` without a query which includes `table2` – Basic Mar 27 '12 at 00:21
Ok I'm not 100% on how mysql handles subqueries as sets, thanks. – hkf Mar 27 '12 at 00:23
1

You can use a subquery that returns several rows with a single column. Selecting the literal numbers like that returns one row with several columns. – Sam Dufel Mar 27 '12 at 00:24

score 2 · Answer 4 · answered Mar 27 '12 at 00:23

2

It's probably easier to do the comparison in php.

$id_list = array('204720966837', '163700377960', '24583115900', '1081617368');

$result = mysql_query('SELECT id FROM ids WHERE id IN  (' . implode(',', $id_list) . ')');
$found = array();
while ($row = mysql_fetch_assoc($result)) {
  $found[] = $result['id'];
}
$diff = array_diff($id_list, $found);

$diff will contain the IDs you were searching for, but didn't find.

answered Mar 27 '12 at 00:23

Sam Dufel

17,560
3
48
51

That is probably the best way to go.. because MySQL isn't made for this kind of operation, where you have only some of the data you're working with in the database and the other at the client. – Niko Mar 27 '12 at 00:49

score 0 · Answer 5 · answered Mar 27 '12 at 00:23

0

TRY

SELECT id FROM ids 
WHERE id != '204720966837' 
AND id != '163700377960'
AND id !='24583115900' 
AND id !='1081617368'

answered Mar 27 '12 at 00:23

prukuhkoo

105
1
2
12

score 0 · Answer 6 · answered Mar 27 '12 at 00:28

0

I would bet that

 WHERE IN ()

does not work either.

Give it a try and that should lead you to your solution.

answered Mar 27 '12 at 00:28

kasavbere

5,873
14
49
72

score 0 · Answer 7 · answered Mar 27 '12 at 05:12

0

Not IN always work with sub-query, may be your Not IN syntex is wrong, correct it and try again.

answered Mar 27 '12 at 05:12

How can I get the opposite of WHERE id IN (...)?

7 Answers7