Compare data in two tables

This query UNIONs queries for matching column names from two tables, and keeps just those rows which occur once in the union. Those are the rows unique to one table or the other. Customise your column list { id, col1, col2, col3 ...} as desired.



SELECT 

  MIN(TableName) as TableName, id, col1, col2, col3, ...

FROM (

  SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...

  FROM a

  UNION ALL

  SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...

  FROM b

) AS tmp

GROUP BY id, col1, col2, col3, ...

HAVING COUNT(*) = 1

ORDER BY ID;