How to select values that do not exist in database MySQL

user_6431533 0
Viewed 191

I wanted to know if it is possible to select values that are not present in the database of MySQL.

The current SQL i have is:

SELECT *
FROM (
    VALUES (1), (2)
) AS ids
WHERE 
    NOT EXISTS ( 
        SELECT *
        FROM data_table dt
        WHERE dt.id = ids.id
    );

The problem for the current SQL is that ids.id column does not exist. I have checked:

  • mysql documentation and there it said that the columns could be accessed as ids.column_0, but that failed.
  • postgresql documentation showed that it could be done with "as ids(id)", but that also failed. Thought maybe MySQL documentation has some unwritten docs.

How to get ids that do not exist in database?

mariadb subquery select mysql sql
1 Answers

In MySQL, you would phrase this as:

SELECT *
FROM (SELECT 1 id UNION ALL SELECT 2) AS ids
WHERE NOT EXISTS (SELECT 1 FROM data_table dt WHERE dt.id = ids.id);

Very recent versions support the VALUES ROW() syntax:

SELECT *
FROM (VALUES ROW(1), ROW(2)) as ids(id)
WHERE NOT EXISTS (SELECT 1 FROM data_table dt WHERE dt.id = ids.id);
user_106767160
Related