This question has already been answered:

Imagine that there is a certain table Users , in which there is an identifier (primary key) with the name id . But for some reason, the id string was ripped out. And it turned out such a table:

id 1,2,3,5,6,7,8,10,12

The question is how to get using only one select set, which includes only the missing numbers. So what is the sql query to do?

Asked the question at the interview, it became very interesting.

Marked as a duplicate by the participants aleksandr barakin , pavel , lexxl , Abyx , cheops 11 Aug '16 at 21:46 .

A similar question was asked earlier and an answer has already been received. If the answers provided are not exhaustive, please ask a new question .

  • specify, i.e. line left, but id is a "cell"? If so then something like select * where id is null or trim (id) = '' - Vyacheslav Potseluyko
  • And to compare this set of numbers is with what? - sp7
  • No, deleted rows and from this lost order of increment. That is, everything went well, but then the lines were pulled out - Polyakov Sergey
  • @ sp7 is not. I also asked such a question)) - Polyakov Sergey
  • five
    Possible duplicate question: How to choose the missing ID? and at least the search is located. And this will be difficult to find ... - pegoopik

3 answers 3

If this is not a range (as in your case), then the problem is solved very trivially and simply. It is enough to make a subquery in WHERE, where there is no following value:

 SELECT (`numbers`.`number`+1) AS `not_exists_number` FROM `numbers` WHERE (SELECT 1 FROM `numbers` AS `add_table` WHERE `add_table`.`number` = (`numbers`.`number` + 1)) IS NULL ORDER BY `numbers`.`number`;

If you really need one SELECT, then we rewrite this solution to JOIN.

 SELECT l.number + 1 AS missing FROM numbers AS l LEFT JOIN numbers AS r ON l.number + 1 = r.number WHERE r.number IS NULL;

The truth here is the problem in two solutions, that the last value will always be greater than the last in the table by 1, but it can be cut off. If you have had the last 26, then it will output another 27, but for some reason it is very simple :)

     DECLARE @ID INT DECLARE @MaxID INT DECLARE @MissingID TABLE ( [ID] INT ) SELECT @MaxID = [ID] FROM Users SET @ID = 1 WHILE @ID <= @MaxID BEGIN IF NOT EXISTS (SELECT * FROM Users WHERE [ID] = @ID) INSERT INTO @MissingID ( [ID] ) VALUES ( @ID ) SET @ID = @ID + 1 END SELECT * FROM @MissingID

      The decision is taken from here . I tried it in MS SQL Server 2008, it works. However, according to your condition, it is not quite clear to me whether nested selects are allowed. :)

       SELECT DISTINCT number FROM master..spt_values WHERE number BETWEEN 1 AND (SELECT MAX(id) FROM MyTable) AND number NOT IN (SELECT id FROM MyTable)
      • If the MyTable table contains more than 2000 (approximately) records, then it will no longer work. - Yaant