SELECT
COALESCE(I.ITEM_CODE, IH.ITEM_CODE) AS CODE,
COALESCE(I.ITEM_NAME, IH.ITEM_NAME) AS NAME,
CASE
WHEN I.ITEM_CODE IS NULL THEN 'DELETED'
WHEN IH.ITEM_CODE IS NULL THEN 'ADDED'
ELSE 'UPDATED'
END AS COMP_RSLT
FROM
ITEM I
FULL OUTER JOIN ITEM_HISTORY IH ON I.ITEM_CODE = IH.ITEM_CODE
ORDER BY
CODE DESC;