CODEWITHSUNDEEP
mysqlsql
jn1kk
jn1kk

Reputation: 5102

Find which values are not in table

Simple question, but I'm drawing a blank. Any help is appreciated.

I have a table of ids:

-------    
| ids |    
-------    
|  1  |    
|  5  |
|  7  |
-------

Except the actual table is thousands of entries long.

I have a list (x), not a table, of other ids, say 2, 6, 7. I need to see which ids from x are not in the ids table.

I need to get back (2,6).

I tried something like this:

SELECT id FROM ids WHERE id IN (2,6,7) GROUP BY id HAVING COUNT(*) = 0;

However, COUNT(*) returns count of retrieved rows only, it doesn't return 0.

Any suggestions?

Upvotes: 1

Views: 71

Answers (2)

SMA
SMA

Reputation: 37023

Try something with "NOT IN" clause:

select * from 
(SELECT 2 as id
UNION ALL
SELECT 6 as id
UNION ALL
SELECT 7 as id) mytable
WHERE ID not in (SELECT id FROM ids)

See fiddle here

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

Create a temporary table, insert the IDs that you need into it, and run a join, like this:

CREATE TEMPORARY TABLE temp_wanted (id BIGINT);
INSERT INTO temp_wanted(id) VALUES (2),(6),(7);
SELECT id
FROM temp_wanted t
LEFT OUTER JOIN ids i ON i.id=t.id
WHERE i.id IS NULL

Upvotes: 2

Related Questions