mysql - Select values from a list that are not in a table

Question

I have a list of ids:

(1, 2, 3, 6, 7)

And a table:

id | anothercolumn
------------------
 1 | NULL
 2 | foo
 4 | bar
 5 | NULL
 6 | NULL

I want to retrieve the values from my list which are not ids of my table.

Expected result:

3, 7

SELECT without FROM

I tried something like this:

SELECT i
WHERE i IN (1, 2, 3, 6, 7)
AND i NOT IN (SELECT id FROM mytable);

But this is not a valid MySQL query (a FROM is required).

UNION

There is also this possibility:

SELECT i
FROM (
    SELECT 1 AS i
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 6
    UNION SELECT 7 ) AS mylistofids
LEFT JOIN mytable
ON mytable.id = i
WHERE mytable.id IS NULL;

This works, but if my list of ids becomes bigger, the query will soon be huge ...

Temporary Table

I can also create a temporary table for my list of ids:

CREATE TEMPORARY TABLE mylistofids (
    i INT
);

INSERT INTO mylistofids (i) VALUES (1), (2), (3), (6), (7);

Then use it in a LEFT JOIN:

SELECT i
FROM mylistofids
LEFT JOIN mytable
ON mytable.id = i
WHERE mytable.id IS NULL;

This works too, but in my case, I don't have the rights to create a table (temporary or not).

Do you see a way to solve this problem in the nicest way possible?

Answer 1

A temp table is the best solution, but if not possible then you can fudge a temp table by selecting constants and unioning them together.

Using this solution you can do the following:-

SELECT i
FROM 
(
    SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 6 UNION SELECT 7
) AS mylistofids 
LEFT JOIN mytable
ON mytable.id = i
WHERE mytable.id IS NULL;

You can also generate a massive range of numbers (assuming that you are dealing with integer ids) by cross joining ranges of numbers. Then just pick the ones that are in the IN clause:-

SELECT i
FROM 
(
    SELECT units.i + tens.i * 10 + hundreds.i * 100 + thousands.i * 1000 + tensthousands.i * 10000 AS i
    FROM (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS units
    CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS tens
    CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS hundreds
    CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS thousands
    CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS tensthousands
) AS mylistofids 
LEFT JOIN mytable
ON mytable.id = i
WHERE mylistofids.i IN (1, 2, 3, 6, 7)
AND mytable.id IS NULL;