MySQL query to find ids that do not exist in table

Question

I have a list of ids pre-generated that I need to check if exist in a table. My table has two columns, id, name, where id is an auto increment integer and name is a varchar(255).

I basically want to get a count of how many ids do not exist in table foo from my pre-generated list. So say my list has the numbers 5 and 10 in it, what's the best way to write something to the extent of:

select count(*) from foo where id does not exist in ( 5, 10 )

The idea here is that if 5 and 10 do not exist, I need the response 2, and not the number of rows in foo that do not have the id 5 or 10.

how about select count(*) from foo where id does not be am in ( 5, 10 ) — Drew
– Drew, Commented Sep 23, 2016 at 19:32

algrebe · Accepted Answer · 2016-09-23 19:58:20Z

TL; DR sample data and queries at rextester

The idea here is that if 5 and 10 do not exist, I need the response 2, and not the number of rows in foo that do not have the id 5 or 10.

You should have provided a little more information to avoid confusion.

Example

id | name 1 | tom 2 | joe 3 | mae 4 | goku 5 | vegeta

If your list contains (1, 2, 3) then your answer should be 0 (since all three are in the table )

If your list contains (1, 2, 6) then your answer should be 1. ( since 1 and 2 are in the table but 6 is in't )

If your list contains (1, 6, 7) then your answer should be 2.

If your list contains (6, 7, 8) then your answer should be 3.

assuming this was your question

If you know the length of your list

select 2 - count(*) as my_count from foo where id in (5, 10)

The following query tells you how many are present in foo.

select count(*) from foo where id in (5,10)

So if you want to find those that do not exist, subtract this result from the length of your list.

select n - count(*) as my_count from foo where id in (5, 10,....)

ScaisEdge · Accepted Answer · 2016-09-23 19:41:21Z

You could use on fly table using union and the a left join

select count(*) from my_table as m left join ( select 5 as id from dual union select 10 from dual ) t on t.id = m.id where t.id is null

otherwise you can populate a tempo table with the value you need and use left join where the value is null

I had to edit my post, what I need is not the rows that do not match those, but the count of how many in the list do not match. So if 5 and 10 do not exist, the query would return 2

DaveL17 · Accepted Answer · 2025-03-04 22:30:17Z

Put your list into temporary table.
then Left join with your foo table and count which not exist.

-- This is your Foo table:

CREATE TEMPORARY TABLE IF NOT EXISTS foo (UNIQUE idx_foo (id), id int); INSERT INTO foo (id) VALUES (1),(2),(3),(4),(5);

-- This is your Check List

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_ids (UNIQUE idx_tmp (id), id int); INSERT INTO tmp_ids (id) VALUES (1),(2),(6);

-- This is Result: 1

SELECT COUNT(t.id) FROM tmp_ids t LEFT JOIN foo f ON t.id=f.id WHERE f.id is null;

Collectives™ on Stack Overflow

MySQL query to find ids that do not exist in table

3 Answers 3

Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Related