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.
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
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).
SELECT 1 UNION ALL SELECT 2 UNION ALL....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)
);
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;
