Select id from input list NOT present in database

With MySql vers 8.0:

CREATE TABLE cnacs(cid VARCHAR(20), PRIMARY KEY(cid));

Then,

INSERT INTO cnacs VALUES('1');

The first two statements execute successfully. The next statement does not, however. My goal is to return a list of unused cid’s from the input table [1, 2]:

SELECT * FROM (VALUES ('1'),('2')) as T(cid) EXCEPT SELECT cid FROM cnacs;

In theory, I’d like the output to be ‘2’, since it has not yet been added. The aforementioned query was inspired by Remus’s answer on https://dba.stackexchange.com/questions/37627/identifying-which-values-do-not-match-a-table-row

Answer

This is at least the correct syntax for what you are trying to do.

If this query is anything more than a learning exercise though I’d rethink the approach, storing these ‘1’ and ‘2’ values (or however many there ends up being) in their own table

SELECT Column_0
  FROM (SELECT * FROM (VALUES ROW('1'), ROW('2')) TMP) VALS
  LEFT
  JOIN cnacs
    ON VALS.Column_0 = cnacs.cid
 WHERE cnacs.cid IS NULL