Given tables setup like this
tableA {
id = 1
name = 'bob'
id = 2
name = 'sally'
id = 3
name = 'sue'
}
tableB {
id = 1
name = 'bob'
}
If I run this command, it returns ID's 2 and 3, as wanted:
select id
from tableA a
left join tableB b using (id)
where id not in (select id from tableB)
order by id
limit 10;
But it is a very large table so I need to use offset. When I try the following command, no results are returned.
select id
from tableA a
left join tableB b using (id)
where id not in (select id from tableB)
order by id
limit 10 offset 10;
Is there a way to use offset when a join is being used?
The process environment is not visible, but I will suggest this approach. You process rows by batches (limit 10).
With parameter @startId=0
select id
from tableA a
where id>@startId
and id not in (select id from tableB)
order by id
limit 10;
After processing batch, take @startId as max(id) in processed batch and put as parameter value no next query - without offset.
select id
from tableA a
where id>@startId
and id not in (select id from tableB)
order by id
limit 10;