-- 追加された商品の一時テーブル作成
CREATE TEMPORARY TABLE tmp_added AS
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 i.ITEM_CODE = ih.ITEM_CODE
WHERE
  ih.ITEM_CODE IS NULL;

-- 更新された商品の一時テーブル作成
CREATE TEMPORARY TABLE tmp_updated AS
SELECT
  i.ITEM_CODE AS CODE,
  i.ITEM_NAME AS NAME,
  "UPDATED" AS COMP_RESULT
FROM
  ITEM i
  INNER JOIN ITEM_HISTORY ih ON i.ITEM_CODE = ih.ITEM_CODE
WHERE
  i.USER_UPDATE_DATETIME > ih.USER_UPDATE_DATETIME;

-- 削除された商品の一時テーブル作成
CREATE TEMPORARY TABLE tmp_deleted AS
SELECT
  ih.ITEM_CODE AS CODE,
  ih.ITEM_NAME AS NAME,
  "DELETED" AS COMP_RESULT
FROM
  ITEM_HISTORY ih
  LEFT OUTER JOIN ITEM i ON ih.ITEM_CODE = i.ITEM_CODE
WHERE
  i.ITEM_CODE IS NULL;

SELECT
  *
FROM
  tmp_added
UNION ALL
SELECT
  *
FROM
  tmp_updated
UNION ALL
SELECT
  *
FROM
  tmp_deleted
ORDER BY
  CODE desc;