Find an invalid record ID in a list of potential ID's

Question

Given a list of potential ID's is there a quick way using a single MYSQL query to work out which, if any, of the ID's do not have an associated record in the database.

e.g. if the list is 1,3,4 and records exist for 1 and 4 but not 3 is there a query that will return 3 from the list.

This needs to be applied to a database containing 15000 records checking against a list of 1 to 100 IDs which may contain zero or more invalid IDs. The list is sourced externally and not in another table.

Answer 1

SELECT idtable.id, records.id as rid FROM idtable 
LEFT JOIN records ON idtable.id = records.id
WHERE rid IS NULL

Answer 2

Sorry about my previous answer - my bad, I read the question too quickly.

There's no clean way to do this in pure SQL (clean meaning not involving ugly subqueries, unions, or temp tables). If it were me, I'd probably do it something like this (assuming PHP):

$all_ids = array(1, 3, 4);
$query = "SELECT id FROM table1 WHERE id IN (" . implode(',', $all_ids) . ")";
$found_ids = getArrayFromQuery($query);
$invalid_ids = array_diff($all_ids, $found_ids);