Select values ​​from a list that is not in the table.

Question

I have a list of identifiers:

(1, 2, 3, 6, 7) 

And the table:

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

I want to get values ​​from my list that are not identifiers 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 an invalid MySQL query (requires <<24>).

UNION

There is also such an opportunity:

 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 identifiers gets larger, the request will be huge soon ...

Temporary table

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

 CREATE TEMPORARY TABLE mylistofids ( i INT ); INSERT INTO mylistofids (i) VALUES (1), (2), (3), (6), (7); 

Then use it in the LEFT JOIN :

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

This also works, but in my case, I do not have permission to create the table (temporary or not).

Do you see a way to solve this problem in the most enjoyable way?

Answer 1

A temp table is the best solution, but if that is not possible, you can pull out a temporary table by selecting constants and combining 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 (provided that you are dealing with integer identifiers) by crossing the ranges of numbers. Then just select the ones that are in the IN section: -

 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;