Return Id’s That Are Not in Database - MYSQL

Question

Problem

In a table X and I own N records, but these records are not sequential (throughout Insert, Id will always be larger than the previous one, but not necessarily the next number).

I need to know which id’s I don’t have in the bank.

Example

SELECT Id FROM X

Would return

Id
1
2
4
8
12

I need to return the Ids 3,5,6,7,9,10,11 that are missing.

I searched a lot, but only found results in English and these results unfortunately did not solve the problem.

Question

How to create a query that can solve this in just a column and without the creation of other tables ?

Answer 1

Try this solution: I played them all ID which exist in a temporary table with the status of "exists"; then, with a counter, checks who does not exist and adds with the status of "there is no".

DROP TEMPORARY TABLE IF EXISTS tmpIds;
CREATE TEMPORARY TABLE tmpIds SELECT Id, 'existe' as 'Status' FROM X

DECLARE maior_id INT DEFAULT 0;
DECLARE contador INT DEFAULT 1;
DECLARE existe varchar(3);

SELECT maior_id = MAX(Id) FROM X

WHILE contador <= maior_id DO
    IF NOT EXISTS (SELECT 1 FROM tmpIds WHERE Id = contador)
        INSERT INTO tmpIds(Id, Status) VALUES(contador, 'nao existe');

    SET contador = contador +1;
END WHILE;

SELECT * FROM tmpIds

Answer 2

Try using this query, if you want to read more here you have link

SELECT a.id + 1 AS start, MIN(b.id) - 1 AS end
    FROM minha_tabela AS a, minha_tabela  AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)

And will return to you the missing records in sequential order

Note id is all tiny, see if your id is also all tiny

Answer 3

Below is a solution in a query:

SELECT
    y.id
FROM
    (
        SELECT
            @row := @row + 1 as id
        FROM 
            (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
            (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
            (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
            (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
            (SELECT @row:=0) AS y
    ) AS y
    LEFT JOIN (SELECT 1 AS id UNION SELECT 2 AS id UNION SELECT 4 AS id UNION SELECT 5 AS id UNION SELECT 12 AS id) AS x ON y.id = x.id
WHERE
    x.id IS NULL
    AND y.id < (SELECT MAX(id) FROM (SELECT 1 AS id UNION SELECT 2 AS id UNION SELECT 4 AS id UNION SELECT 5 AS id UNION SELECT 12 AS id) AS x)

You would only need to replace the lattices (SELECT 1 AS id UNION SELECT 2 AS id UNION SELECT 4 AS id UNION SELECT 5 AS id UNION SELECT 12 AS id) with your bank table. And this solution would have a limitation of up to 10,000 records.