getting values which dont exist in mysql table

Question

I have a set of values(keys), 1,2,3,4,5 and following table:

id key
------
1  2
2  8
3  4

If I need to check which of the given keys are in db, I use:

SELECT * 
  FROM mytable 
 WHERE key IN (1,2,3,4,5)

So I'd get rows with keys 2 and 4.

Is there some sort of mysql way of finding out which of the given keys are NOT in db? So in this case I'd like to get 1,3,5.

This is not duplicate of linked question, not at all – mvp Jan 02 '13 at 06:44 — mvp, Jan 02 '13 at 06:44

mvp · Accepted Answer · 2013-01-02T01:50:44.730

5

Create temporary table with your keys:

CREATE TEMPORARY TABLE mykeys (`key` INT);
INSERT INTO mykeys VALUES (1),(2),(3),(4),(5);

Then use NOT IN:

SELECT `key`
FROM mykeys
WHERE `key` NOT IN (SELECT `key` FROM mytable)

Finally, drop your TEMP table if you must:

DROP TABLE mykeys

EDIT: Added SQLFiddle.

If you are using PostgreSQL which supports EXCEPT operator, and also VALUES statement can be used to create row set from list of values, there is another, easier way to do this without temporary tables (SQLFiddle):

VALUES (1),(2),(3),(4),(5)
EXCEPT
SELECT key FROM mytable

edited Jan 02 '13 at 01:50

answered Jan 01 '13 at 23:00

mvp

111,019
13
122
148

4

@OMGPonies Not by my reading - given the `IN ()` list `IN (1,2,3,4,5)`, the OP wants the values from that list which don't exist, not rows which don't exist in the list (which `NOT IN()` would return). – Michael Berkowski Jan 01 '13 at 23:02
@MichaelBerkowski: Apologies -- you're correct, too much eggnog. An alternative would be to create an inline view using `SELECT 1 UNION ALL SELECT 2 UNION ALL...`. – OMG Ponies Jan 01 '13 at 23:37

Trent Earl · Answer 2 · 2013-01-01T23:12:37.133

1

While this is a very messy solution it does work:

select v.val from
(
  select 1 as val
  union
  select 2 as val
  union
  select 3 as val
  union
  select 4 as val
  union
  select 5 as val
) v
WHERE
v.val NOT IN(
  SELECT `key` 
  FROM mytable 
  WHERE key IN (1,2,3,4,5)
);

edited Jan 01 '13 at 23:12

answered Jan 01 '13 at 22:55

Trent Earl

3,517
1
15
20

score 1 · Answer 3 · answered Jan 01 '13 at 23:18

This is a bit clunky, but it does work:

select sq.val
from   mytable t
       right join (select 1 as val
                   union 
                   select 2
                   union 
                   select 3
                   union 
                   select 4
                   union 
                   select 5 ) as sq
 on t.col = sq.val
 where t.id is null;

Obligatory SQL Fiddle.

getting values which dont exist in mysql table

3 Answers3

Linked

Related