SQL - Comparing two tables with Minus - Include/Exclude temp field

Question

After carefully reading a lot of topics about comparing tables and using the minus function I'm posting this.

I've got my comparison between two tables running.

Select Column1,Column2.. from table 1
minus Column1,Column2.. from table 2
union all
Column1,Column2.. from table 2
Select Column1,Column2.. from table 1
order by column1

Now this gives me a list of duplicate or single values that are different in each table. This is fine. However I do not have an indicator telling me in which table the (faulty) rows are. I tried adding a temporary column giving it an A and B field. This results in a full export of the table because this obviously gets taken in with the minus function.

Is there a way that I can tag the rows telling me what table they are in without adding a permanent column in the table,because this is not an option.

Help is much appreciated!

Answer 1

I would phrase this as a union of left joins:

SELECT t1.col1, t1.col2, 'table1' AS label
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.col1 = t2.col1 AND t1.col2 = t2.col2
WHERE t2.col1 IS NULL
UNION ALL
SELECT t2.col1, t2.col2, 'table2'
FROM table2 t2
LEFT JOIN table1 t1
    ON t1.col1 = t2.col1 AND t1.col2 = t2.col2
WHERE t1.col1 IS NULL

The label column is computed during the UNION and serves to label the origin table for each record (i.e. set of values) which are unique to that particular table. Note that you can extend what I have given above by adding the necessary number of columns to fill both tables.

This is a general solution which should work across most RDBMS, and doesn't rely on any set difference operators.

Demo here:

SQLFiddle

Answer 2

The example query you provided isn't syntactically correct and has errors when running. But, based on the description of what you tried, I think I understand what you're trying to accomplish.

You were on the right track with adding a temporary column that provides an indicator of which table is the source of the row. The value of the temporary column should be the same for the queries before the UNION ALL and a different value for the queries after.

Here's an updated version of your example query. You can try it out at SqlFiddle

(
  SELECT 'FromTable1', COLUMN1, COLUMN2, COLUMN3 FROM TABLE1
  EXCEPT
  SELECT 'FromTable1', COLUMN1, COLUMN2, COLUMN3 FROM TABLE2
)
UNION ALL
(
  SELECT 'FromTable2', COLUMN1, COLUMN2, COLUMN3 FROM TABLE2
  EXCEPT
  SELECT 'FromTable2', COLUMN1, COLUMN2, COLUMN3 FROM TABLE1
)