Select IDs not in database

Go To


I have an array of products IDs and I need to find the IDs that are not present in the database. Sure I can do it like this:

// IDs in database: 1, 2, 3, 4, 5.
$products = array(4, 5, 6, 7);

$in_db = $db->exec("SELECT `id` FROM `table` WHERE `id` IN (" . implode($products) . ");");    
$in_db_ids = array();

foreach($in_db as $i){
    $in_db_ids[] = $i['id'];

$missing = array_diff($products, $in_db_ids);

But this is long and boring. I thought also about something like this:

// this query would be generated with PHP using an implode or something
$query = "
    SELECT `t`.`id` as `missing`
    LEFT JOIN `table` USING(`id`)
    WHERE `missing` is NULL;
$missing = $db->exec($query);

But this is so inelegant. I think there should be a proper way to write that SELECT x UNION SELECT y UNION SELECT z, or there may be another nice way to do this check. What do you think, guys?

2012-04-04 01:36
by s3v3n
Because of MySQL, the only other option is to create a NUMBERS table -- a single INT column, populated with values from one to whatever you think you need. If it were Oracle or SQL server, you could use ROW_NUMBER/etc.. - OMG Ponies 2012-04-04 01:40
This would be less efficient than both examples I poste - s3v3n 2012-04-04 01:42
A table, that could be indexed? ; - OMG Ponies 2012-04-04 01:48
The cost that I will need to pay is not the selection, but the insertion. Indexes will affect this even worse - s3v3n 2012-04-04 01:50
You create the table once. Google the "SQL numbers table".. - OMG Ponies 2012-04-04 03:43
Now I get it. Thank you very much : - s3v3n 2012-04-04 04:23


To do it in the MySQL database, you will need a table or a temporary table that has a number of rows equal to the highest index in the table you are scanning for missing blanks. There is no way around this -- MySQL always "loops" through tables, it isn't a procedural language where you can set up your own loop.

That being said, if you create or have a table of sufficient size, you can create the natural number sequence using a user variable. Let's call the big table bigtable (it doesn't matter what columns are in it - we just need a name of one of the columns - I use 'acolumn').

set @row = 0;
select n from 
  (select @row := @row + 1 as n from bigtable) as t 
left join mytable on = t.n 
where mytable.acolumn is null;

Let me know if this needs a little more explanation.

2012-04-04 02:05
by D Mac
Well, my UNION-s would create that table in memory. How is your answer different than mine except the fact that your table is not only in memory and keeping in mind what I discussed with mr @OMG Ponies in question comments about costs - s3v3n 2012-04-04 02:19
And I find this quite less elegant than doing the UNIONS, and even inefficien - s3v3n 2012-04-04 02:21
@OMG Ponies comment "You create the table once" helped me understand what do you mean. Thank you : - s3v3n 2012-04-04 04:24