Select values ​​that are not in the table

Question

MySQL

Hello.

In the table, the item_id column values ​​consist of numbers 1, 2, 3, 4. I have a list of numbers 2, 3, 5. I need a query that will return only 5, that is, the value of which is not in the table but is in the query.

I do not understand how to make such a request?
It is clear when the data is in two tables, but when the list is in the query, the query becomes not obvious.

Answer 1

uh...

SELECT * 
FROM table 
WHERE 
    item_id IN (2, 3, 5) 
AND 
    item_id NOT IN (SELECT item_id FROM table);

Answer 2

And if to create the temporary table?

Answer 3

Collect from 2,3,5 temp table. Doing SELECT a.id my_tmp_table as a LEFT JOIN my_values ​​as b on a.id=b.id WHERE isnull(b.id) is the fastest option and will run in reasonable time even for a large number of elements.

Answer 4

Does anyone remember the EXCEPT statement?
SELECT 2 AS item_id
UNION
SELECT 3 AS item_id
UNION
SELECT 5 AS item_id
EXCEPT
SELECT item_id FROM table

Answer 5

First thing that came to mind (MySQL):
SELECT tmp.id
FROM (
SELECT 2 AS id
UNION
SELECT 3 AS id
UNION
SELECT 5 AS id
) tmp
LEFT JOIN table tc ON ( t.item_id = tmp.id )
WHERE t.item_id IS NULL

Answer 6

You can also do this:

SET @var = '{2}{3}{5}';
SELECT 
SUBSTRING
(
    @var := REPLACE
    (
        (	
            SELECT @var := REPLACE(@var, concat('{', `item_id`, '}'), '') AS `ids`
            FROM `table` 
            ORDER BY `ids` DESC 
            LIMIT 0,1
        ),
        '}{', 
        ','
    ), 
    2, 
    length(@var) - 2
)

The string '{2}{3}{5}' is generated by the script.
For one entry, the query will return '5', for multiple entries, for example '5,6,7'.
In MySQL works for me.