Don't Panic Don't Panic - 6 months ago 9
MySQL Question

Can I find which values in an array do not have corresponding database records using only one query?

I have an array of a few values that I get from an uploaded file. I would like to identify which values in the file are not in my database.

I already know how to check the values one at a time by looping over the array and checking for matching records from the database for each value.

$stmt = $pdo->prepare('SELECT COUNT(*) FROM my_table WHERE a_column = ?');
foreach ($values as $value) {
$stmt->execute([$value]);
if ($stmt->fetchColumn() === '0') // do something with $value
}


But I was wondering if there is a way to get the results in one query. If I already had the values in an SQL table, it would be easy to find which ones had matching values in another table by joining the tables and checking the results for null values in the table in question.

SELECT i.value
FROM
imaginary_values_table i
LEFT JOIN my_table m ON i.value = m.a_column
WHERE
m.id IS NULL


Is it possible to do this type of query using a list of values instead?

I am familiar with
IN
, but I don't see how it could be used for this directly, because I am trying to find which items in an array are not in a table. With
IN
I could get all the rows from the table that are in my array, or with
NOT IN
I could get all the rows that are not in my array.

Answer

You can use a query like this:

SELECT t1.val
FROM (SELECT 1 AS val
      UNION
      SELECT 2 AS val
      UNION
      SELECT 3 AS val
      ...) AS t1
LEFT JOIN my_table AS t2 ON t1.val = t2.a_column
WHERE t2.a_column IS NULL

You can build the UNION subquery from your array of values:

$union = implode(' UNION ', array_map(function($val) {
    return "SELECT $val AS val";
}, $array));