-- 追加されたアイテムを取得する
SELECT
  ITEM_CODE AS CODE,
  ITEM_NAME AS NAME,
  'ADDED' AS COMP_RSLT
FROM ITEM
WHERE ITEM_CODE NOT IN (SELECT ITEM_CODE FROM ITEM_HISTORY)

UNION ALL

-- 更新されたアイテムを取得する
SELECT
  ITEM.ITEM_CODE AS CODE,
  ITEM.ITEM_NAME AS NAME,
  'UPDATED' AS COMP_RSLT
FROM ITEM
INNER JOIN ITEM_HISTORY ON ITEM.ITEM_CODE = ITEM_HISTORY.ITEM_CODE
WHERE ITEM.USER_UPDATE_DATETIME <> ITEM_HISTORY.USER_UPDATE_DATETIME

UNION ALL

-- 削除されたアイテムを取得する
SELECT
  ITEM_HISTORY.ITEM_CODE AS CODE,
  ITEM_HISTORY.ITEM_NAME AS NAME,
  'DELETED' AS COMP_RSLT
FROM ITEM_HISTORY
WHERE ITEM_HISTORY.ITEM_CODE NOT IN (SELECT ITEM_CODE FROM ITEM)

ORDER BY CODE DESC;