-- 共通のITEM_CODEを持つレコードを抽出
WITH common_item_codes AS (
    SELECT DISTINCT item_code
    FROM item
    UNION
    SELECT DISTINCT item_code
    FROM item_history
)
-- 共通のITEM_CODEを持つレコードのみを比較
SELECT
    i.item_code AS CODE,
    i.item_name AS NAME,
    CASE
        WHEN ih.item_code IS NULL THEN 'ADDED'
        WHEN i.item_code IS NULL THEN 'DELETED'
        WHEN ih.item_code IS NOT NULL AND i.user_update_datetime = ih.user_update_datetime THEN NULL
        WHEN ih.item_code IS NOT NULL AND i.user_update_datetime <> ih.user_update_datetime THEN 'UPDATED'
    END AS COMP_RSLT
FROM item AS i
LEFT JOIN item_history AS ih ON i.item_code = ih.item_code
AND i.item_code IN (SELECT item_code FROM common_item_codes)
where COMP_RSLT is not null
ORDER BY code DESC;