I have a big table A, a big table B, and a little table C.
I want to find the first 1000 rows in A that don't exist in B or C.
I know how to find the first 1000 rows in A that don't exist in B:
INSERT INTO D
SELECT a.*
FROM a NATURAL LEFT JOIN b
WHERE b.some_id IS NULL
ORDER BY a.time DESC
LIMIT 1000;
The above works fine for me. But now I have to take C into account.
If I have to separate the B pass into an intermediate stage, I may
have to keep repeating B passes until the C pass has enough rows in
it. So, I would like to do just a single step to avoid having all
that extra logic.
Is the following correct if I also have C:
INSERT INTO D
SELECT a.*
FROM a NATURAL LEFT JOIN b NATURAL LEFT JOIN c
WHERE b.some_id IS NULL AND c.some_id IS NULL
ORDER BY a.time DESC
LIMIT 1000;
What about this:
INSERT INTO D
SELECT a.*
FROM a NATURAL LEFT JOIN b, c
WHERE b.some_id IS NULL AND c.some_id IS NULL
ORDER BY a.time DESC
LIMIT 1000;
Thanks,
Dave