Presto SQL - to compare 2 different table datasets AND only return exceptions or differences (reconciling)

Question

I am trying to build presto SQL query to read two different tables such the output will simpify the manual reconile work and only return the differences or expection.

1. Read the reference id, and return expection in the output (for ex. reference in table A but not in table B OR reference not in table A but in table B)
2. Read the reference id, and return expection in the output (for ex. references are in table A and table B, but the value shows differences)

Table A

Reference ID A	Value A
001	5
002	6
003	7
004	8

Table B

Reference ID B	Value B
001	5
002	6
003	6
005	9

Since Reference ID 001 and 002 are reconciled, so both IDs should not include in the output.

However, for for reference ID 003 there are differences in the value, therefore this should include in the output.

Similar for reference 004 and 005, both references IDs only appear in either of the tables.

Output

Reference ID A	Value A	Reference ID B	Value B	Commentary
003	7	003	6	Value differences
004	8	null	null	Not in table B
null	null	005	9	Not in table A

Answer 1

This suggests a full join, then filtering on unmatched pairs. We can use a case expression to categorize the offending records.

Assuming that both tables have columns id and val:

select *
from (
    select a.id id_a, b.id id_b
        a.val val_a, b.val val_b,
        case 
            when a.id is null then 'not in table a'
            when b.id is null then 'not in table b'
            when a.val is distinct from b.val then 'values differ'
        end as reason
    from tablea a
    full join tableb b on b.id = a.id and b.val = a.val
) t
where reason is not null