MariaDB: get the intersection of two queries results in two tables

Question

So I have two tables, both have the same structure:

tableA	tableB
ID	ID
1	1
3	2
5	3
10	5

What I need is to compare tableA.ID and tableB.ID and find out which IDs are free in both table and get the intervals of those free IDs.

To find out free IDs in one table I use this:

SELECT
    a.ID + 1 start,
    min(b.ID) - 1 end,
    min(b.ID) - a.ID - 1 gap
   
FROM
   tableA a,
   tableB b
   
WHERE      a.ID < b.ID
GROUP BY   a.ID
HAVING     start < MIN(b.ID)

It works fine, I get my gaps:

tableA

start	end	gap
2	2	1
4	4	1
6	9	4

tableB

start	end	gap
4	4	1

But then I need to compare the results and check which IDs are free in both tables. The result should look something like this:

start	end	gap
4	4	1
6	9	4

And here I'm honestly lost and I don't have any ideas what to do. I've tried to use join in, but it doesn't work for me:

WHERE a.ID < b.ID AND a.ID IN (

SELECT
    c.ID+1 startID,
    min(d.ID) - 1 endID,
    min(d.ID) - c.ID - 1 gap
   
from
   tableB c,
   tableB d

where      c.rowid < d.rowid
)

@ahmed, I'm sorry for my mistake, I've edited the post. It's MariaDB 10.4.26 — nopinopa, Oct 27 '22 at 17:32
@GRIV, there is no row with ID = 4 in both tables, so this ID is free — nopinopa, Oct 27 '22 at 17:32

score 3 · Accepted Answer · answered Oct 27 '22 at 17:40

You may union the two tables to get the unique values from both, then use LEAD function to find the free IDs as the following:

WITH reserved_IDs AS
(
  SELECT ID FROM tableA
  UNION 
  SELECT ID FROM tableB
  UNION SELECT 0 /* To start from 1 in case 1 value is missing from both tables*/
)
SELECT start_id, end_id, gap
FROM
(
  SELECT ID+1 AS start_id, 
         LEAD(ID) OVER (ORDER BY ID) - 1 AS end_id,
         LEAD(ID) OVER (ORDER BY ID)-ID-1 gap 
  FROM reserved_IDs
) T
WHERE gap > 0

See a demo.

MariaDB: get the intersection of two queries results in two tables

1 Answers1