-- 追加されたデータを取得
SELECT
    I.ITEM_CODE AS CODE,
    I.ITEM_NAME AS NAME,
    'ADDED' AS COMP_RSLT
FROM
    ITEM I
    LEFT OUTER JOIN ITEM_HISTORY IH ON IH.ITEM_CODE = I.ITEM_CODE
WHERE
    IH.ITEM_CODE IS NULL


UNION ALL

-- 更新されたデータを取得
SELECT
    I.ITEM_CODE AS CODE,
    I.ITEM_NAME AS NAME,
    'UPDATED' AS COMP_RSLT
FROM
    ITEM I
    INNER JOIN ITEM_HISTORY IH ON IH.ITEM_CODE = I.ITEM_CODE
WHERE
    IH.USER_UPDATE_DATETIME < I.USER_UPDATE_DATETIME

UNION ALL

-- 削除されたデータを取得
SELECT
    IH.ITEM_CODE AS CODE,
    IH.ITEM_NAME AS NAME,
    'DELETED' AS COMP_RSLT
FROM
    ITEM_HISTORY IH
    LEFT OUTER JOIN ITEM I ON IH.ITEM_CODE = I.ITEM_CODE
WHERE
    I.ITEM_CODE IS NULL

ORDER BY
    CODE DESC;