mysql - Select ID which has not been exist in between -

select id table id between 1 , 5 

the result of query above be: 1,2,3,4,5

if id 1 , id 2 exist in table, loop (1,2) only.

it means, if id between 1 , 5, id of 3,4,5 doesn't exist.

the id of 3,4,5 want select. how this?

thanks in advance.

try this:

select *  (    select 1 val    union    select 2    union    select 3     union    select 4    union    select 5 )t left join tablename tn on t.val = tn.id tn.id null 

with tally table:

select * ( select (3 + th*1000+h*100+t*10+u+1) x (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 tn.x between 3 , 10 , t.id null 

notice in formula (3 + th*1000+h*100+t*10+u+1) , where clause 3 srart, 10 end. change on variables.

here fiddle: http://sqlfiddle.com/#!9/2f53f/2