SELECT
ITEM.ITEM_CODE AS CODE,
ITEM.ITEM_NAME AS NAME,
CASE 
    WHEN ITEM_HISTORY.ITEM_CODE IS NUll AND  ITEM.ITEM_CODE IS NOT NULL  THEN 'DELETED'
    WHEN ITEM_HISTORY.ITEM_CODE IS NOT NUll AND  ITEM.ITEM_CODE IS NULL    THEN 'UPDATED'
    WHEN ITEM.USER_UPDATE_DATETIME > ITEM_HISTORY.USER_UPDATE_DATETIME   THEN 'ADDED'
    
END AS COMP_RSLT
FROM ITEM LEFT JOIN ITEM_HISTORY ON ITEM.ITEM_CODE = ITEM_HISTORY.ITEM_CODE