with list as (
    select
        ITEM.ITEM_CODE as CODE,
        ITEM.ITEM_NAME as NAME,
        ITEM.USER_UPDATE_DATETIME as date,
        ITEM_HISTORY.ITEM_CODE as hCODE,
        ITEM_HISTORY.ITEM_NAME as hNAME,
        ITEM_HISTORY.USER_UPDATE_DATETIME as hdate
    from ITEM left join ITEM_HISTORY on ITEM.ITEM_CODE = ITEM_HISTORY.ITEM_CODE
    union
    select
        ITEM.ITEM_CODE as CODE,
        ITEM.ITEM_NAME as NAME,
        ITEM.USER_UPDATE_DATETIME as date,
        ITEM_HISTORY.ITEM_CODE as hCODE,
        ITEM_HISTORY.ITEM_NAME as hNAME,
        ITEM_HISTORY.USER_UPDATE_DATETIME as hdate
    from ITEM_HISTORY left join ITEM on ITEM.ITEM_CODE = ITEM_HISTORY.ITEM_CODE
)
select
    case
        when CODE is not null then CODE
        else hCODE
    end as CODE
    ,
    case
        when NAME is not null then NAME
        else hNAME
    end as NAME,
    case
        when CODE is null AND hCODE is not null then 'ADDED'
        when CODE is not null AND hCODE is null then 'DELETED'
        else 'UPDATED'
    end as COMP_RSLT
from list
order by CODE
;