MySQL query to find ids that do not exist in table

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.

Answers


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,....)

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


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

SELECT COUNT(*) FROM foo WHERE id not in ( 5, 10 );


Need Your Help

Spring: When getting a ManyToOne entity, reference entity (OneToMany) is not showing in JSON

java json spring spring-mvc spring-data-jpa

When I send a GET request in POSTMAN to get all my child entity (Town) the parent entity (Province) is not shown in the JSON response.

change the 2nd column from positive to negative and vice versa

bash unix sed awk

How can i change the 2nd column from positive to negative and vice versa