select 
ifnull(item.ITEM_CODE, item_history.item_code) as CODE
, ifnull(item.ITEM_NAME, item_history.item_name) as NAME
, case 
when item.item_code is null then 'DELETED'
when item_history.item_code is null then 'ADDED'
else 'UPDATED'
end as COMP_RSLT
from item full join item_history on item.ITEM_CODE = item_history.ITEM_CODE
where item.USER_UPDATE_DATETIME is not item_history.USER_UPDATE_DATETIME
order by CODE desc