Oracle SQL - Filter values in a table using date ranges available in another table-CodePudding

I am trying to come up with a Oracle SQL query to list all rows in "External_Order" table in which the order_dt value does not fall within start_dt and end_dt values in orders table for the same cust_id. Is there a way to achieve this using SQL without having to write PL/SQL?

**ORDERS**

CUST_ID  , START_DT,     END_DT
12345     , 01-01-2022,   01-15-2022
12345     , 02-01-2022,   02-28-2022

**EXTERNAL_ORDER**

CUST_ID, ORDER_DT,   AMOUNT
12345,   01-10-2022,  100
12345,   01-16-2022,  200
12345,   01-27-2022,  150
12345,   02-03-2022,  300

**EXPECTED OUTPUT**

CUST_ID  ORDER_ID ,   AMOUNT    
12345,   01-16-2022,  200
12345,   01-27-2022,  150

CodePudding user response：

Perhaps EXISTS might do the trick?

SELECT eo.*
FROM   EXTERNAL_ORDER eo 
WHERE  NOT EXISTS ( 
       SELECT 1
       FROM   ORDERS o 
       WHERE  eo.CUST_ID = o.CUST_ID
       AND    o.START_DT <= eo.ORDER_DT 
       AND    o.END_DT >= eo.ORDER_DT

)

demo db<>fiddle