set - 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?

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.