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

by

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.

More Related Contents:

  1. How do I specify unique constraint for multiple columns in MySQL?
  2. Create table variable in MySQL
  3. Mysql: Select rows from a table that are not in another
  4. How to import a CSV file into a MySQL table
  5. Maximum number of records in a MySQL database table
  6. MySQL > Table doesn’t exist. But it does (or it should)
  7. How can I simulate an array variable in MySQL?
  8. How to select from MySQL where Table name is Variable
  9. Which is more efficient: Multiple MySQL tables or one large table?
  10. Is there any reason to worry about the column order in a table?
  11. How to store Query Result in variable using mysql
  12. Delimiters in MySQL
  13. How to store Emoji Character in MySQL Database
  14. MySQL: Quick breakdown of the types of joins [duplicate]
  15. Any way to select without causing locking in MySQL?
  16. Log all queries in mysql
  17. Cast from VARCHAR to INT – MySQL
  18. MySQL SELECT WHERE datetime matches day (and not necessarily time)
  19. Adding multiple columns AFTER a specific column in MySQL
  20. MySQL varchar index length
  21. Ruby on Rails 3 Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ on OSX
  22. MYSQL_ROOT_PASSWORD is set but getting “Access denied for user ‘root’@’localhost’ (using password: YES)” in docker container
  23. MySQL – Fetching lowest value
  24. What are differences between INSERT and UPDATE in MySQL?
  25. How to alias a field or column in MySQL?
  26. mysql: why comparing a ‘string’ to 0 gives true?
  27. base64 encode in MySQL
  28. ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysql.sock’ (2)
  29. How do you force mysql LIKE to be case sensitive? [duplicate]
  30. MySQL Alter table causes Error: Invalid use of NULL value

Leave a Comment