How to get new products and deleted products back from price history table?

Question

Oracle

I have a table with 5 unique columns Id, Date, Shop, Product, Price. I can show all changes Price

SELECT 1 ID, '09.01.2018' "DATE", 'CandyShop' Shop, 'Pie' Product, 10 Price FROM DUAL UNION
SELECT 2, '10.01.2018', 'CandyShop', 'Pie', 11 FROM DUAL UNION
SELECT 3, '11.01.2018', 'CandyShop', 'Pie', 10 FROM DUAL UNION
SELECT 4, '12.01.2018', 'CandyShop', 'Pie', 9 FROM DUAL UNION

SELECT 5, '10.01.2018', 'CandyShop', 'Candy', 9 FROM DUAL UNION
SELECT 6, '11.01.2018', 'CandyShop', 'Candy', 9 FROM DUAL

But how to show new products? I want to get row id 5. Since it was not in ChandyShop on 09/01/2018. This is a new product.

how to show removed products? I want to get row id 6. Because it was not in CandyShop on 12/01/2018 but 11/01/2018 was. This is a remote product.

Answer 1

select
*
from (select ... union...) as qq
where ...