How to select values ​​not found in the table?

Question

I would like to identify specific identifiers that are not in the table.

For example, I have identifiers 1, 2, and 3 and you want to know if they exist in the table.

Essentially it comes down to:

SELECT id FROM (
    SELECT 1 AS id
      UNION
    SELECT 2 AS id
      UNION
    SELECT 3 AS id
)
WHERE
    NOT EXISTS (SELECT * FROM table WHERE table.id = id)

Suppose you tablehad identifiers 1 and 4, then this would give 2 and 3.

Is there a more elegant / compressed / faster way to get these IDs in SQLite?

Answer 1

the compound SELECT EXCEPT statement allows you to do something similar to NOT EXISTS:

SELECT 1 AS id UNION ALL
SELECT 2       UNION ALL
SELECT 3
EXCEPT
SELECT id FROM MyTable

Starting with SQLite 3.8.3, you can use VALUES wherever you could use SELECT, but this is just a different syntax:

VALUES (1),
       (2),
       (3)
EXCEPT
SELECT id FROM MyTable