Oracle SQL to Find missing number in given range of values

 

 

 Expected Output:

 

 

 

DROP TABLE A;        

CREATE TABLE A (NAME VARCHAR2(10 BYTE), S_START NUMBER, S_END NUMBER);       

Insert into A (NAME,S_START,S_END) values ('serial 1',1,10);         

Insert into A (NAME,S_START,S_END) values ('serial 2',15,20);        

         

DROP TABLE B         

  CREATE TABLE B         

   (    NAME VARCHAR2(10 BYTE),          

    S_VAL NUMBER         

   );        

Insert into B (NAME,S_VAL) values ('serial 1',1);         

Insert into B (NAME,S_VAL) values ('serial 1',2);         

Insert into B (NAME,S_VAL) values ('serial 1',3);         

Insert into B (NAME,S_VAL) values ('serial 1',6);         

Insert into B (NAME,S_VAL) values ('serial 1',8);         

Insert into B (NAME,S_VAL) values ('serial 1',10);        

Insert into B (NAME,S_VAL) values ('serial 2',16);        

Insert into B (NAME,S_VAL) values ('serial 2',17);     

 

select * from a;

select * from b;

         

select name,s_start+l-1 from a,lateral       

(select level l from dual connect by level <= a.s_end - a.s_start+1)         

minus        

select * from b;   

 

select * from a,

lateral(select level l from dual connect by level<=a.s_end-a.s_start+1);

 

select name,s_start+l-1 from a,

lateral(select level l from dual connect by level<=a.s_end-a.s_start+1);

 

select name,(l+a.s_start-1) val from a,

lateral(select level l from dual connect by level<=a.s_end-a.s_start+1)

minus

select * from b;