Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
0


CREATE TABLE A
( id number(10) NOT NULL,
  item_id number(10),
  start_num number(10) not null,
  end_num number(10) not null
);
CREATE TABLE B
( id number(10) NOT NULL,
  item_id number(10),
  start_num number(10) not null,
  end_num number(10) not null
);
INSERT INTO A (ID, ITEM_ID, START_NUM, END_NUM) VALUES (1, 337, 101, 400);
1 rows affected

INSERT INTO A (ID, ITEM_ID, START_NUM, END_NUM) VALUES (2, 337, 500, 800);
1 rows affected

INSERT INTO A (ID, ITEM_ID, START_NUM, END_NUM) VALUES (3, 337, 801, 1200);
1 rows affected

INSERT INTO A (ID, ITEM_ID, START_NUM, END_NUM) VALUES (4, 337, 1500, 1600);
1 rows affected

INSERT INTO A (ID, ITEM_ID, START_NUM, END_NUM) VALUES (5, 337, 15000, 16000);
1 rows affected

INSERT INTO A (ID, ITEM_ID, START_NUM, END_NUM) VALUES (6, 337, 20000, 30000);
1 rows affected

INSERT INTO A (ID, ITEM_ID, START_NUM, END_NUM) VALUES (7, 444, 20, 30);
1 rows affected

select * from a;


ID
ITEM_ID
START_NUM
END_NUM


1
337
101
400

2
337
500
800

3
337
801
1200

4
337
1500
1600

5
337
15000
16000

6
337
20000
30000

7
444
20
30


INSERT INTO B (ID, ITEM_ID, START_NUM, END_NUM) VALUES(1, 337, 240, 300);
1 rows affected

INSERT INTO B (ID, ITEM_ID, START_NUM, END_NUM) VALUES(2, 337, 301, 400);
1 rows affected

INSERT INTO B (ID, ITEM_ID, START_NUM, END_NUM) VALUES(3, 337, 850, 1100);
1 rows affected

INSERT INTO B (ID, ITEM_ID, START_NUM, END_NUM) VALUES(4, 337, 1500, 1510);
1 rows affected

INSERT INTO B (ID, ITEM_ID, START_NUM, END_NUM) VALUES(5, 337, 15000, 16000);
1 rows affected

select * from b;


ID
ITEM_ID
START_NUM
END_NUM


1
337
240
300

2
337
301
400

3
337
850
1100

4
337
1500
1510

5
337
15000
16000


/* 
   assumptions: a.start_num is never be greater then b.start_num
                a.end_num is never less than b.end_num


   this leaves...


*/
select 
  a.item_id as item_num,
  a.start_num as a_start_num,
  a.end_num as a_end_num,
  b.start_num as b_start_num,
  b.end_num as b_end_num
from a 
inner join b 
on a.item_id = b.item_id
  where
    a.start_num <= b.start_num and
    a.end_num >= b.end_num
union
select 
  a.item_id as item_num,
  a.start_num as a_start_num,
  a.end_num as a_end_num,
  b.start_num as b_start_num,
  b.end_num as b_end_num
from a 
left join b
  on a.item_id = b.item_id
  where
   b.item_id is null
;


ITEM_NUM
A_START_NUM
A_END_NUM
B_START_NUM
B_END_NUM


337
101
400
240
300

337
101
400
301
400

337
801
1200
850
1100

337
1500
1600
1500
1510

337
15000
16000
15000
16000

444
20
30
null
null

ID	ITEM_ID	START_NUM	END_NUM
1	337	101	400
2	337	500	800
3	337	801	1200
4	337	1500	1600
5	337	15000	16000
6	337	20000	30000
7	444	20	30

ITEM_NUM	A_START_NUM	A_END_NUM	B_START_NUM	B_END_NUM
337	101	400	240	300
337	101	400	301	400
337	801	1200	850	1100
337	1500	1600	1500	1510
337	15000	16000	15000	16000
444	20	30	null	null