mysql offset doesn't work with multiple tables

Question

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?

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
– AztecCodes, Commented Apr 3 at 15:22
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
– user3052443, Commented Apr 3 at 15:35
How many rows returned in case "very large table" without offset and limit? — ValNik
– ValNik, Commented Apr 3 at 15:53
About 67,000. A lot of processing is done on the data so the script times out. — user3052443
– user3052443, Commented Apr 3 at 16:12

ValNik · Accepted Answer · 2025-04-04 16:16:02Z

1

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

edited Apr 4 at 16:16

answered Apr 3 at 17:32

ValNik

7,8681 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql offset doesn't work with multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related