Don't Panic Don't Panic - 2 years ago 59
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) {
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
imaginary_values_table i
LEFT JOIN my_table m ON i.value = m.a_column

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

I am familiar with
, 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
I could get all the rows from the table that are in my array, or with
I could get all the rows that are not in my array.

Answer Source

You can use a query like this:

SELECT t1.val
      SELECT 2 AS val
      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));
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download