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`
    SELECT 4 as `id` UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
    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?

• php
• mysql
• arrays
• select

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 mytable.id = t.n 
where mytable.acolumn is null;

Let me know if this needs a little more explanation.