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?

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?

• mysql
• left-join
• offset

Share Improve this question Follow edited 19 hours ago ValNik 6,13411 gold badge77 silver badges1515 bronze badges asked 19 hours ago user3052443user3052443 84811 gold badge1010 silver badges2323 bronze badges 11

• You can use OFFSET with a JOIN, this is not the issue. The reason why you're getting no results after adding OFFSET 10 in the specific query you provided is because of how filtering is done after the JOIN and WHERE clause, especially combined with NOT IN and the effect of your dataset size. – AztecCodes Commented 19 hours ago
• I probably don't understand your answer but I tried moving the limit and offset to the subquery and it failed with "This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'". Can you provide an example of how it can work? – user3052443 Commented 19 hours ago
• How many rows returned in case "very large table" without offset and limit? – ValNik Commented 19 hours ago
• About 67,000. A lot of processing is done on the data so the script times out. – user3052443 Commented 18 hours ago
• Do you have indexes on tableA(id) and tableB(id)? – ValNik Commented 18 hours ago

 |  Show 6 more comments

1 Answer 1

Sorted by: Reset to default 0

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;