using EXCEPT or INTERSECT to only show mismatched rows

Question

Let's say I have 2 result sets (2 queries).

FIELDNAME   VALUE
field1      20.00
field2      13.00
field3      4.00

FIELDNAME   VALUE
field1      20.00
field2      14.00
field3      6.00

I know query1 EXCEPT query2 should give

FIELDNAME   VALUE
field2      13.00
field3      4.00

But what I really want is to show any cases where there is a difference, from both sides of the query:

FIELDNAME   VALUE
field2      13.00
field3      4.00
field2      14.00
field3      6.00

Is that possible? I suppose I could do a SELECT UNION into a temp table. Then delete any rows where NOT EXISTS fieldname with different value. Anything simpler?

It seems like I could combine the INTERSECT, UNION and EXCEPT somehow and end up with this, but not having much luck conceptualizing.

Answer 1

I think you should be able to get what you need by using EXCEPT twice and using UNION ALL on the results:

-- records from the table1 that are not in table2
(SELECT * FROM table1 
EXCEPT 
SELECT * FROM table2)

UNION ALL

-- records from the table2 that are not in table1
(SELECT * FROM table2 
EXCEPT 
SELECT * FROM table1)

Other approach is to get combination of all tables using UNION, then use EXCEPT to eliminate all intersecting records:

-- Union of both tables
(SELECT * FROM table1 
UNION ALL
SELECT * FROM table2)

EXCEPT   -- Exclude the records ...

-- ... that are in both tables
(SELECT * FROM table1 
INTERSECT 
SELECT * FROM table2)