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