Roku: what this merge would actually do

Constraints

1. 1change_type is exactly one of 'insert', 'update', 'unchanged' or 'delete'.
2. 2insert means the device is in staging only; delete means it is in the target only.
3. 3update means the device is in both and any of firmware, region or last_seen_on differs.
4. 4region is nullable on both sides. Two NULLs are the same value for this purpose, and NULL against a value is a change.
5. 5Every device in either table appears exactly once — 13 rows in total.
6. 6Order by device_id.

Worked example

Device d04 has region NULL in the target and 'DE' in staging: a real update. Device d02 has NULL on both sides: unchanged. Write the comparison as s.region <> t.region and SQL answers unknown for both, because any comparison involving NULL is unknown rather than true or false. The CASE falls through to 'unchanged', and d04 and d05 are reported as needing no work. The merge then leaves stale regions in the warehouse and nothing in the output says so.

What this tests

NULL-safe comparison and set completeness together: <> cannot detect a change involving NULL, and a LEFT JOIN cannot see rows that exist only in the target.