MySQL - Select from a list of numbers those without a counterpart in the id field of a table

Question

I have a list of numbers, say {2,4,5,6,7} I have a table, foos, with foos.ID, including say, {1,2,3,4,8,9}

Id like to take my list of numbers, and find those without a counterpart in the ID field of my table.

One way to achieve this would be to create a table bars, loaded with {2,4,5,6,7} in the ID field. Then, I would do

SELECT bars.* FROM bars LEFT JOIN foos ON bars.ID = foos.ID WHERE foos.ID IS NULL

However, I'd like to accomplish this sans temp table.

Anyone have any input on how it might happen?

Ecospecies

From the above example, you need to get 5 and 6, right? – Palanquin

Yes, Im looking for 5,6,7 - Sorry, I meant to put that in here. – Ecospecies

Answer 1

This is a problem that is pretty common: generating a relation on the fly without creating a table. SQL solutions for this problem are pretty awkward. One example using a derived table:

SELECT n.id
FROM
  (SELECT 2 AS id 
   UNION SELECT 3 
   UNION SELECT 4 
   UNION SELECT 5 
   UNION SELECT 6 
   UNION SELECT 7) AS n
  LEFT OUTER JOIN foos USING (id)
WHERE foos.id IS NULL;

But this doesn't scale very well, because you might have many values instead of just six. It can become tiresome to construct a long list with one UNION needed per value.

Another solution is to keep a general-purpose table of ten digits on hand, and use it repeatedly for multiple purposes.

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT n.id
FROM 
  (SELECT n1.i + n10.i*10 AS id
   FROM num AS n1 CROSS JOIN num AS n10
   WHERE n1.i + n10.i*10 IN (2, 3, 4, 5, 6, 7)) AS n
  LEFT OUTER JOIN foos USING (id)
WHERE foos.id IS NULL;

I show the inner query generating values from 0..99 even though this isn't necessary for this case. But you might have values greater than 10 in your list. The point is that with one table num, you can generate large numbers without having to resort to very long chains with one UNION per value. Also, you can specify the list of desired values in one place, which is more convenient and readable.

Smollett

Sadly, the numbers being used are arbitrary in size and range. That rules out your second option. The first is a bit kludgy but also, generating relations without a table isn't the nicest thing in the nation. I was really hoping to get at some set-based logic, but mysql's SET seems weak. – Ecospecies

This solution worked for me. If you're dealing with duplicate values, use union all select. – Abednego

After 8 years this case is still a problem :). – Ishmaelite

The year is 2023 and AFIK MySQL still doesn't have a way of nicely making a list of values relational :facepalm: – Plumbery

@craastad, You can generate a series of numbers using a CTE, as shown on this manual page: dev.mysql.com/doc/refman/8.0/en/… – Smollett

Answer 2

I can't find a solution to your precise problem that doesn't use a temporary table, but an alternate way of doing your query using a sub-select instead of a join is:

SELECT bars.* FROM bars WHERE bars.ID NOT IN (SELECT ID FROM foos)

Like the other posters I originally wrote:

SELECT * FROM foos WHERE foos.ID NOT IN (2, 4, 5, 6, 7)

but then I realised that this is producing the opposite to what you want.

Blandishment

Yep, the temp table is the way to go. And you are right - we were too fast to read :) Thanks, I'm deleting my post. – Palanquin

Too bad, Id rather not create a temp table... Looks like my best bet, however. Thanks! – Ecospecies

Answer 3

If you use PHP, you can make this work without creating any temporary tables.

SELECT ID FROM foos WHERE foos.ID IN (2, 4, 5, 6, 7)

You can use PHP's array_diff() function to convert this to the desired result. If your list (2,4,5,6,7) is in an array called $list and the result of the query above is in an array $result, then

$no_counterparts = array_diff($list, $result);

...will return all the numbers in your list with no counterpart in your database table. While this solution doesn't perform the entire operation within the query, the post-processing you need to do in PHP is minimal to get what you want, and it may be worthwhile to avoid having to create a temporary table.

Trager

Answer 4

I had a similar problem. I had a range where the auto-incrementing primary key had some missing values, so first I found how many there were: select count(*) from node where nid > 1962. Comparing this number against the highest value, I got the number missing. Then I ran this query: select n2.nid from node n1 right join node n2 on n1.nid = (n2.nid - 1) where n1.nid is null and n2.nid > 1962 This will find the number of non-consecutive missing records. It won't show consecutive ones, and I'm not entirely certain how to do that, beyond changing the ON clause to allow greater latitude (which would make the JOIN table substantially larger). In any case, this gave me five results out of the total seven missing, and the other two were guaranteed to be next to at least one of the five. If you have a larger number missing, you'll probably need some other way of finding the remaining missing.

Poindexter

Answer 5

Alnitak's (and yours) solution should work, and I can not thing about anything else which can work only in SQL language.

But here comes the question - how do you pass the list of values? Isn't it better to handle this in the calling code - i.e. request the ID's and compare it in the colling code, which may be in a language better suited for this kind of manipulations.

Palanquin

Answer 6

Happened to reach here searching for an answer. Earlier posts are pre MySQL 8. Since MySQL has value statement since version 8.0.19 , the question can be very elegantly solved using value statement along with CTE which is also available since MySQL 8.0

Step 1: Combine CTE and value statement to create a set of rows with the values that needs to be compared to the table (here the table is foo).

with MyValues(val) as
(
  values row(2),row(4),row(5),row(6),row(7)
)

Step 2: Outer join the CTE with the table foo and filter the rows which has the null values from the CTE after the outer join with foo

WITH myvalues(val)
     AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
SELECT f.id
FROM   foo f
       LEFT OUTER JOIN myvalues m
                    ON f.id = m.val
WHERE  m.val IS NULL; 

Trail

mysql> WITH myvalues(val)
    ->      AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
    -> SELECT f.id
    -> FROM   foo f
    ->        LEFT OUTER JOIN myvalues m
    ->                     ON f.id = m.val
    -> WHERE  m.val IS NULL;
+------+
| id   |
+------+
|    1 |
|    3 |
|    8 |
|    9 |
+------+
4 rows in set (0.00 sec)

Or using IN clause

mysql> WITH myvalues(val)
    ->      AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
    -> SELECT f.id
    -> FROM   foo f
    -> WHERE  id NOT IN (SELECT val
    ->                   FROM   myvalues);
+------+
| id   |
+------+
|    1 |
|    3 |
|    8 |
|    9 |
+------+
4 rows in set (0.00 sec)

Gaseous