WAQ TO Find out the new order ID inserted & deleted order id from last refresh copy

 #15day SQL CHALLENGE

DAY-2
--
--The order table is there every day at 12AM IST , a copy of the table to be taken (Copy of Order table) .
--So find out the new order ID inserted & deleted order id from 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 REPLICATE('*' , 200) as '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---------------------------------------