Пересекающиеся диапазоны в Oracle SQL и включают также непересекающиеся диапазоны

Вы можете развернуть наборы данных и объединить их, а затем использовать аналитическую функцию LEAD, чтобы найти следующую границу в диапазоне, а затем использовать рекурсивный запрос для перебора границ и агрегирования, добавления или удаления значений по мере приближения к началу или концу. соответственно их диапазонов:

WITH data (value, start_end, bound, dataset) AS (
  SELECT value, start_end, bound, 1
  FROM   dataset1
  UNPIVOT (bound FOR start_end IN ("FROM" AS 1, "TO" AS -1)) d
UNION ALL
  SELECT value, start_end, bound, 2
  FROM   dataset2
  UNPIVOT (bound FOR start_end IN ("FROM" AS 1, "TO" AS -1)) d
),
bounds (value, start_end, bound, next_bound, rn) AS (
  SELECT value,
         start_end,
         bound,
         LEAD(bound) OVER (ORDER BY bound, start_end, dataset),
         ROW_NUMBER() OVER (ORDER BY bound, start_end, dataset)
  FROM   data
),
groups (value, bound, next_bound, rn) AS (
  SELECT value, bound, next_bound, rn
  FROM   bounds
  WHERE  rn = 1
UNION ALL
  SELECT CASE b.start_end
         WHEN 1
         THEN g.value || b.value
         ELSE REPLACE(g.value, b.value)
         END,
         b.bound,
         b.next_bound,
         b.rn
  FROM   bounds b
         INNER JOIN groups g
         ON (g.rn + 1 = b.rn)
)
SELECT value,
       bound AS "FROM",
       next_bound AS "TO"
FROM   groups
WHERE  bound < next_bound
ORDER BY rn;

Что для примера данных:

CREATE TABLE Dataset1 ("FROM", "TO", Value) AS
  SELECT   0,  20, 'A' FROM DUAL UNION ALL
  SELECT  20,  50, 'B' FROM DUAL UNION ALL
  SELECT  50, 150, 'C' FROM DUAL UNION ALL
  SELECT 150, 180, 'X' FROM DUAL;

CREATE TABLE Dataset2 ("FROM", "TO", Value) AS
  SELECT  10,  30, 'D' FROM DUAL UNION ALL
  SELECT  70, 100, 'E' FROM DUAL;

Выходы:

рабочий пример

with un as (
  select * from ds1 union all select * from ds2),
up as (
  select distinct rng from un unpivot (rng for col in (r1, r2))),
ld as (
  select rng r1, lead(rng) over (order by rng) r2 from up)
select ld.r1, ld.r2, listagg(value) within group (order by value) list 
  from ld join un on un.r1 < ld.r2 and ld.r1 < un.r2
  group by ld.r1, ld.r2

дбфиддл

Описание:

• un - объединение двух таблиц
• up - несводная un, только один столбец всех отдельных диапазонов
• ld - выше со следующим значением диапазонов

В конце ld соединяется с un и ld, listagg() группирует значения

Вы можете попробовать этот запрос

WITH intersecting AS (
       SELECT GREATEST(d1.StartRange, d2.StartRange) AS StartRange, 
              LEAST(d1.EndRange, d2.EndRange) AS EndRange,
              CONCAT(d1.Value, d2.Value) AS Value, 
              d1.StartRange AS StartRange1,
              d1.EndRange AS EndRange1
       FROM   Dataset1 d1
              INNER JOIN Dataset2 d2 
                ON d1.StartRange <= d2.EndRange AND d1.EndRange >= d2.StartRange
     ),
     partially_intersecting AS (
       -- get left side remain range, filter out empty range which having start = end
       SELECT d1.StartRange AS StartRange,
              COALESCE(i1.StartRange, d1.EndRange)  AS EndRange,
              d1.Value
       FROM   Dataset1 d1
              LEFT JOIN intersecting i1 ON d1.StartRange = i1.StartRange1
       WHERE  d1.StartRange <> COALESCE(i1.StartRange, d1.EndRange)
       UNION
       -- get right side remain range, filter out empty range which having start = end 
       SELECT COALESCE(i2.EndRange, d1.StartRange)  AS StartRange,
              d1.EndRange AS EndRange,
              d1.Value
       FROM   Dataset1 d1       
              LEFT JOIN intersecting i2 ON d1.EndRange = i2.EndRange1
       WHERE  d1.EndRange <> COALESCE(i2.EndRange, d1.StartRange)
     ),
     output_data AS (
       SELECT StartRange, EndRange, Value FROM intersecting
       UNION ALL 
       SELECT StartRange, EndRange, Value FROM partially_intersecting
     )

SELECT * FROM output_data ORDER BY StartRange

Смотрите демо здесь