Intersecting ranges in Oracle SQL and include also not intersecting ranges

I cannot figure out a solution the following problem:

I have two datasets containing ranges and a value, e.g.

Dataset1

Dataset2

I want to intersect these ranges but also include all ranges from Dataset1 using SQL.

So the result should be:

With SQL it is easy to find only the intersecting parts (10-20, 20-30, 70-100) and the completely not intersecting parts (150-180) - I am struggling with the partially intersecting parts (0-10, 30-50, 50-70, 100-150).

I used this for the intersecting parts:

SELECT GREATEST(t1.range_start, t2.range_start) AS intersect_start, 
       LEAST(t1.range_end, t2.range_end) AS intersect_end
FROM ranges t1
JOIN ranges t2 ON t1.range_start < t2.range_end AND t1.range_end > t2.range_start

Could someone point me to the solution? Would be nice in plain SQL, but could also be in PL/SQL

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

dbfiddle

Description:

• un - union of two tables
• up - unpivoted un, just one column of all distinct ranges
• ld - above with next value of ranges

At the end ld is joined with un and ld, listagg() groups values

You can unpivot the datasets and combine them and then use the LEAD analytic function to find the next bound in the range and then use a recursive query to iterate over the bounds and aggregate, adding or removing values, as it comes to the start or end, respectively, of their ranges:

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;

Which, for the sample data:

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;

Outputs:

fiddle

You could try this query

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

See demo here