Return which of the query items are not in the table?

Question

Is there any way to return which query items are not in a table?

Let’s say I have an appointment like this:

SELECT id FROM tabela WHERE id IN (1,23,45,68,91,126,345,418,509,610);

This will bring me a table with the records of the query that are present system. But I want to know which of these records were deleted, so I need the items that are not present in the system.

How to resolve this without having to make a query for each record?

You could solve this by creating a temporary table with the searched values and do a LEFT JOIN with the system table searching for NULL in comparison. But it would have some simpler way to return the missing values?

Answer 1

You could solve this by creating a temporary table with the searched values and do a LEFT JOIN with the system table searching for NULL in comparison. But it would have some simpler way to return the missing values?

In my opinion, the temporary table is the simplest output. An alternative is to use a lot of UNION, but it looks pretty ugly:

SELECT id FROM
   (SELECT 1 AS id UNION SELECT 23 AS id UNION SELECT 45 AS id /* etc */) tbl
WHERE id NOT IN (SELECT id FROM tabela)

Demo in http://sqlfiddle.com/#! 2/812b5/12

---

You can also use an interesting trick described in this answer of Bacchus to the question How to generate numerical sequences in SQL without creating tables? The idea is to keep an integer table in your bank, with 10 rows (the numbers from 0 to 9):

CREATE TABLE inteiros (i INT);
INSERT INTO inteiros (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

With this table you can simulate a generator, since Mysql does not support native generators. For example, you can generate numbers from 1 to 1000 like this:

SELECT d3.i*100+d2.i*10+d1.i+1 AS gerador
FROM inteiros AS d1
JOIN inteiros AS d2
JOIN inteiros AS d3;

Applying this to your example, and considering that the ids to be checked are in this range from 0 to 1000, the following query generates the list:

SELECT d3.i*100+d2.i*10+d1.i+1 AS gerador
FROM inteiros AS d1
JOIN inteiros AS d2
JOIN inteiros AS d3
HAVING gerador IN (1,23,45,68,91,126,345,418,509,610);

Finally, using this to get the result you want:

SELECT id FROM (
    SELECT d3.i*100+d2.i*10+d1.i+1 AS id
    FROM inteiros AS d1
    JOIN inteiros AS d2
    JOIN inteiros AS d3
    HAVING gerador IN (1,23,45,68,91,126,345,418,509,610);
) tbl
WHERE id NOT IN (SELECT id FROM tabela)

Answer 2

I could use something like that

SELECT id FROM tabela WHERE id NOT IN (1,23,45,68,91,126,345,418,509,610);