Select ids that don’t exist in a table

I have an ids range (it defines by startId and stopId). I have a table with some records(every record has an id – primaty key). Now I need to select all ids from specified range that don’t exist in the table. I am using postgres database. Please suggest what options I have to perform such query.

Answer

You may look at generate_series() function.

Then use an except clause to get the difference.

select s.a from generate_series(<start>, <stop>) as s(a)
except
select id from <myTable>
where <yourClause>
--order by a

See SqlFiddle

Leave a Reply

Your email address will not be published. Required fields are marked *