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

Navigation
3

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

Let's say I have a query 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 query records that are present on the system. But I want to know which records have been deleted, so I need the items that are not in the system.

How to solve this without having to make an appointment for each record?

I could solve this by creating a temporary table with the searched values and make a LEFT JOIN with the system table looking for NULL in the comparison. But would it have some simpler way of returning missing values?

    
asked by anonymous 01.10.2014 / 20:42

2 answers

4
  

I could solve this by creating a temporary table with the searched values and make a LEFT JOIN with the system table looking for NULL in the comparison. But would it have some simpler way of returning missing values?

In my opinion, the temporary table is the simplest output. An alternative is to use a lot of UNION , but it looks really 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 on link

You can also use an interesting trick described in this answer from Bacco to the question How to generate numeric strings in SQL The idea is to keep an integer table with 10 rows (the numbers from 0 to 9) in your database: 

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 give native support to generators. For example, you can generate the 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)
    
01.10.2014 / 21:00
-1

I could use something like this 

SELECT id FROM tabela WHERE id NOT IN (1,23,45,68,91,126,345,418,509,610);
    
01.10.2014 / 20:44
How popular is an array with the registered bank records? How do I give an echo without a line break?