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
)
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.