Find missing numeric values ​​in a range

Question

I read several articles that one of the mistakes a regular programmer does not use the potential of SQL, and since then I started looking for replacing parts of my code with SQLish solutions, and not with data selection and processing in a programming language, although I am a true rookie with SQL.

Let's say I have a table randomly populated with values ​​from 0 to 10, and I want to know what values ​​are missing in this range.

For example, the table consists of the following: 0, 1, 3, 4, 5, 7, 8, 9.

The query must return: 2, 6, 10.

Answer 1

[F5] solution (assuming sql server):

-- table with id=0..10 
drop table #temp 
GO
create table #temp (
    id int not null identity(0,1),
    x int
)
GO
insert into #temp (x) values(0)
GO 11


-- your number:
drop table #numbers
GO
select
    *
into #numbers
from (
    select 0 as n union all select  1 union all select  3 union all select  4 union all select  5 union all select  7 union all select  8 union all select  9
) x
GO

-- result:
select 
    * 
from #temp t
left join #numbers n
    on t.id=n.n
where 1=1
    and n.n is null 

Answer 2

SQL-- ( AFAIK GO SQL Server Management Studio)

, ( ):

CREATE FUNCTION dbo.GetNumbersInRange(@Min INT, @Max INT)
        RETURNS @trackingItems TABLE (Number INT) 
        AS BEGIN

        DECLARE @counter INT = @Min

        WHILE (@counter <= @Max)
        BEGIN
          INSERT INTO @trackingItems (Number) SELECT @counter

          SELECT @counter = @counter + 1

        END

        RETURN

        END
        GO

, ( )

CREATE TABLE MyNumbers (Number INT)

INSERT INTO MyNumbers (Number)
  SELECT 1
  UNION
  SELECT 2
  UNION
  SELECT 4
  UNION
  SELECT 5
  UNION
  SELECT 7
  UNION
  SELECT 8

, LEFT JOIN,

SELECT
        AllNumbers.Number
      FROM GetNumbersInRange(1, 10) AS AllNumbers
      LEFT JOIN MyNumbers ON AllNumbers.Number = MyNumbers.Number
      WHERE MyNumbers.Number IS NULL