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. ...

Select id from input list NOT present in database

With MySql vers 8.0:

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

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

Then,

INSERT INTO cnacs VALUES('1');

 
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;

 
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

 
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​

Advertisement

Answer