Deleted order id & inserted order id from last refresh copy

 --The order table is there, every day at 12AM IST , a copy of the table is to be taken (Copy of Order table) .

--To find out the new order ID  inserted & deleted order id from the last refresh copy 

---------------------------SAMPLE DATA CREATED----------

create table tbl_orders (

order_id integer,

order_date date

);

insert into tbl_orders

values (1,'2022-10-21'),(2,'2022-10-22'),

(3,'2022-10-25'),(4,'2022-10-25');

select * into tbl_orders_copy from  tbl_orders;

select * from tbl_orders;

insert into tbl_orders

values (5,'2022-10-26'),(6,'2022-10-26');

delete from tbl_orders where order_id=1;

SELECT * FROM tbl_orders

SELECT * FROM tbl_orders_copy

--------------------------------------------SOLUTION-----------------------

SELECT B.Table_Copy_Order_ID ,CASE WHEN B.Table_Order_id IS NULL THEN 'D' else '0' END as 'STATUS'      FROM 
(
SELECT tbl.order_id  as 'Table_Order_id',tbl_c.order_id as 'Table_Copy_Order_ID'  FROM tbl_orders tbl
RIGHT   JOIN tbl_orders_copy tbl_c
on tbl.order_id=tbl_c.order_id --and tbl_c.order_id is null 
) B WHERE B.Table_Order_id IS NULL 

UNION ALL 

SELECT A.Table_Order_id ,CASE WHEN A.Table_Copy_Order_ID IS NULL THEN 'I' else '0' END as 'STATUS'      FROM 
(
SELECT tbl.order_id  as 'Table_Order_id',tbl_c.order_id as 'Table_Copy_Order_ID'  FROM tbl_orders tbl
LEFT   JOIN tbl_orders_copy tbl_c
on tbl.order_id=tbl_c.order_id --and tbl_c.order_id is null 
) A WHERE A.Table_Copy_Order_ID IS NULL 

--------------------------END OF SOLUTION---------------------------------------