mysqlleft-joinoffset

mysql offset doesn't work with multiple tables


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?


Solution

  • 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;
    

    Example