WITH T_added AS ( 
    -- 追加分
    SELECT
         ITEM.ITEM_CODE AS CODE
        ,ITEM.ITEM_NAME AS NAME
        ,ITEM.USER_UPDATE_DATETIME AS it
        ,ITEM_HISTORY.USER_UPDATE_DATETIME as it_h
        ,'ADDED' AS COMP_RSLT
    FROM
        ITEM
        LEFT JOIN ITEM_HISTORY ON ITEM_HISTORY.ITEM_CODE = ITEM.ITEM_CODE
    WHERE
        ITEM_HISTORY.ITEM_CODE is NULL 
) 
,T_deleted AS ( 
    -- 削除分
    SELECT
         ITEM_HISTORY.ITEM_CODE AS CODE
        ,ITEM_HISTORY.ITEM_NAME AS NAME
        ,ITEM.USER_UPDATE_DATETIME as it
        ,ITEM_HISTORY.USER_UPDATE_DATETIME as it_h
        , 'DELETED'  AS COMP_RSLT
    FROM
        ITEM_HISTORY
        LEFT JOIN ITEM ON ITEM_HISTORY.ITEM_CODE = ITEM.ITEM_CODE
    WHERE
        ITEM.ITEM_CODE is NULL 
) 
,T_updated AS ( 
    -- 更新分
    SELECT
         ITEM.ITEM_CODE AS CODE
        ,ITEM.ITEM_NAME AS NAME
        ,ITEM.USER_UPDATE_DATETIME as it
        ,ITEM_HISTORY.USER_UPDATE_DATETIME as it_h
        , 'UPDATED' AS COMP_RSLT
    FROM
        ITEM
        LEFT JOIN ITEM_HISTORY ON ITEM_HISTORY.ITEM_CODE = ITEM.ITEM_CODE
    WHERE
        ITEM.USER_UPDATE_DATETIME <> ITEM_HISTORY.USER_UPDATE_DATETIME
) 

SELECT
      Ta.CODE AS CODE
    , Ta.NAME AS NAME
--    ,Ta.it as it
    ,Ta.COMP_RSLT as COMP_RSLT
FROM
    T_added as Ta
union
SELECT
      Td.CODE AS CODE
    , Td.NAME AS NAME
--    ,Td.it as it
    ,Td.COMP_RSLT as COMP_RSLT
FROM
    T_deleted as Td
union
SELECT
      Tu.CODE as CODE
    , Tu.NAME as NAME
--    ,Tu.it as it
    ,Tu.COMP_RSLT as COMP_RSLT
FROM
    T_updated as Tu
    
ORDER BY
        CODE deSC
;