sqlmariadbmariadb-10.4

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


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
)

Solution

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