SELECT that returns list of values not occurring in any row

If you don't want to (explicitly) use temporary tables, this will work:

SELECT id FROM (
  (SELECT 1 AS id) UNION ALL
  (SELECT 2 AS id) UNION ALL
  (SELECT 3 AS id) UNION ALL
  (SELECT 4 AS id) UNION ALL
  (SELECT 5 AS id)
) AS list
LEFT JOIN users USING (id)
WHERE users.id IS NULL

However, it is quite ugly, quite long, and I am dubious about how it would perform if the list of IDs is long.

Had the same need and built on the answer by BugFinder using a temporary table in session. This way it will automatically be destroyed after I'm done with the query, so I don't have to deal with house cleaning as I will run this type of query often.

Create the temporary table:

CREATE TEMPORARY TABLE tmp_table (id INT UNSIGNED);

Populate tmp_table with the values you will check:

INSERT INTO tmp_table (id) values (1),(2),(3),(4),(5);

With the table created and populated, run the query as with any regular table:

SELECT tmp_table.id
  FROM tmp_table
  LEFT JOIN users u
  ON tmp_table.id = u.id
  WHERE u.id IS NULL;

This info on MySQL Temporary Tables was also useful

Given the numbers are a fixed list. Quickest way I can think of is have a test table, populated with those numbers and do

untested select statement - but you will follow the princpal.

select test.number 
from test 
left join 
    users 
on 
    test.number = users.id 
where test.number <> users.id

Then you'll get back all the numbers that dont have a matching user.id and so can fill in the holes..