How to find the missing number?

In the id column there are numbers, say, from 1 to 10 in ascending order. One of the numbers is missing.
The search algorithm is kind of how it is: to check the difference between the values if the difference is greater than one, output (previous + 1) value. How to register select syntax..?
April 3rd 20 at 18:24
2 answers
April 3rd 20 at 18:26
Solution
SELECT t1.id + 1 FROM <tablename> t1 WHERE NOT EXISTS(SELECT 'x' FROM <tablename> t2 WHERE t2.id = t1.id + 1)
and if it is appropriate use t2 if only one table? - sidney.Gusikowski commented on April 3rd 20 at 18:29
@sidney.Gusikowski, this is one table - Bradley.Wiega commented on April 3rd 20 at 18:32
@Bradley.Wiega, could you briefly explain what in this case means 'x', what is the value..? - sidney.Gusikowski commented on April 3rd 20 at 18:35
@sidney.Gusikowski, just a text constant. the subquery must return a single value, no matter what - Bradley.Wiega commented on April 3rd 20 at 18:38
Ah, then I understand, thank you - sidney.Gusikowski commented on April 3rd 20 at 18:41
April 3rd 20 at 18:28
The option to use window functions: lag, lead
-- lag
select x.id x.id_lag
from (
 SELECT id,
 lag(id, 1) over (order by id) as id_lag
 FROM test) x;

select x.id x.lag_diff
from (
 SELECT id,
 id - lag(id, 1) over (order by id) as lag_diff
 FROM test) x
where x.lag_diff > 1;

-- lead
select x.id x.id_lead
from (
 SELECT id,
 lead(id, 1) over (order by id) as id_lead
 FROM test) x;

select x.id x.lead_diff
from (
 SELECT id,
 lead(id, 1) over (order by id) - id as lead_diff
 FROM test) x
where x.lead_diff > 1;

https://www.db-fiddle.com/f/dBpCb4ieea1UQcVPn39QTq/2

Find more questions by tags SQL