Select an identifier that does not exist between
SELECT ID FROM TABLE WHERE ID BETWEEN 1 AND 5
The result of the query above will be: 1,2,3,4,5
If only exist id
1
and id
2
that exist in the table, then it will only cycle (1,2)
.
This means that if id
is in between 1 AND 5
, id
of 3,4,5
does not exist.
id
3,4,5
is what I want to choose. How to do it?
Thanks in advance.
source to share
Try the following:
select * from
(
select 1 as val
union all
select 2
union all
select 3
union all
select 4
union all
select 5
)t
left join TableName tn on t.val = tn.id
where tn.id is null
From tally table
:
select * from (
select (3 + th*1000+h*100+t*10+u+1) x from
(select 0 th union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) A,
(select 0 h union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) B,
(select 0 t union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) C,
(select 0 u union select 1 union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union select 9) D)
tn
left join t on tn.x = t.id
where tn.x between 3 and 10
and t.id is null
Note that in the formula (3 + th*1000+h*100+t*10+u+1)
and where
paragraph 3 is equal srart
to 10 - end
. Change of variables.
Here is a fiddle: http://sqlfiddle.com/#!9/2f53f/2
source to share