Select where number range does not overlap

oracleoracle-12cselect

I have two tables that contain records about road construction activites:

  • table_a is the master list.
  • table_b is a legacy list.

For each road, in each year, I want to select the records from table_b that do not already exist in table_a.

Also, the records should not overlap spatially along the road. More specifically, the from_m and to_m of the records in table_b should not overlap the from_m and to_m in table_a.

How can I do this? I do not have Oracle Spatial.

The data in Excel (for easy viewing):

Here is what the data looks like in Excel:

enter image description here

The records in green should be selected by the query; the records in red should not.

The DDL:

Table A:

  create table table_a 
   (
    id number(4,0), 
    road_id number(4,0), 
    year number(4,0), 
    from_m number(4,0), 
    to_m number(4,0)
   );

insert into table_a (id,road_id,year,from_m,to_m) values (1,1,2000,0,100);
insert into table_a (id,road_id,year,from_m,to_m) values (2,1,2005,0,25);
insert into table_a (id,road_id,year,from_m,to_m) values (3,1,2005,50,75);
insert into table_a (id,road_id,year,from_m,to_m) values (4,1,2005,75,100);
insert into table_a (id,road_id,year,from_m,to_m) values (5,1,2010,10,50);
insert into table_a (id,road_id,year,from_m,to_m) values (6,1,2010,50,90);
insert into table_a (id,road_id,year,from_m,to_m) values (7,1,2015,40,100);
insert into table_a (id,road_id,year,from_m,to_m) values (8,2,2020,0,40);
insert into table_a (id,road_id,year,from_m,to_m) values (9,2,2020,0,40);
insert into table_a (id,road_id,year,from_m,to_m) values (10,3,2025,90,150);
commit;

select * from table_a;

        ID    ROAD_ID       YEAR     FROM_M       TO_M
---------- ---------- ---------- ---------- ----------
         1          1       2000          0        100
         2          1       2005          0         25
         3          1       2005         50         75
         4          1       2005         75        100
         5          1       2010         10         50
         6          1       2010         50         90
         7          1       2015         40        100
         8          2       2020          0         40
         9          2       2020          0         40
        10          3       2025         90        150

Table B:

  create table table_b 
   (
   id number(4,0), 
    road_id number(4,0), 
    year number(4,0), 
    from_m number(4,0), 
    to_m number(4,0)
   );

insert into table_b (id,road_id,year,from_m,to_m) values (1,1,1995,0,100);
insert into table_b (id,road_id,year,from_m,to_m) values (2,1,2001,0,50);
insert into table_b (id,road_id,year,from_m,to_m) values (3,1,2005,20,80);
insert into table_b (id,road_id,year,from_m,to_m) values (4,1,2005,0,100);
insert into table_b (id,road_id,year,from_m,to_m) values (5,1,2010,0,10);
insert into table_b (id,road_id,year,from_m,to_m) values (6,1,2010,90,100);
insert into table_b (id,road_id,year,from_m,to_m) values (7,1,2010,5,85);
insert into table_b (id,road_id,year,from_m,to_m) values (8,1,2015,40,100);
insert into table_b (id,road_id,year,from_m,to_m) values (9,1,2015,0,40);
insert into table_b (id,road_id,year,from_m,to_m) values (10,2,2020,0,41);
insert into table_b (id,road_id,year,from_m,to_m) values (11,3,2025,155,200);
insert into table_b (id,road_id,year,from_m,to_m) values (12,3,2025,199,300);
insert into table_b (id,road_id,year,from_m,to_m) values (13,4,2024,5,355);
commit;

select * from table_b;

        ID    ROAD_ID       YEAR     FROM_M       TO_M
---------- ---------- ---------- ---------- ----------
         1          1       1995          0        100
         2          1       2001          0         50
         3          1       2005         20         80
         4          1       2005          0        100
         5          1       2010          0         10
         6          1       2010         90        100
         7          1       2010          5         85
         8          1       2015         40        100
         9          1       2015          0         40
        10          2       2020          0         41
        11          3       2025        155        200
        12          3       2025        199        300
        13          4       2024          5        355

Best Answer

I think you want this, an anti-semijoin (also often called just "anti-join").

The "semi" because there is a join between a and b (in the subquery) but we only want and get rows from one table (b) in the result. The "anti" because we get rows from one table that do NOT have a related row in the other table.:

select b.*                             -- show everything
from table_b b                         -- from table b
where not exists                       -- when
      ( select 1                       -- there is nothing
        from table_a a                 -- in table a with
        where a.road_id = b.road_id    -- same road,
          and a.year = b.year          -- same year and
          and a.from_m < b.to_m        -- overlapping
          and b.from_m < a.to_m        -- (from, to)
      ) ;

Test at sqlfiddle.com.

Related Question