Check whether combinations of columns match in both tables

Question

I got two tables which I want to compare. First it has to compare the test1 column. If there is a value in the first table which doenst exist in the second table and vice versa, it has to show those values in the result. And this needs to be done for every column.

For example:

first table:
------------------------------
| id | test1 | test2 | test3 | 
------------------------------
| 1  | 1     | 1     | 1     |
------------------------------
| 2  | 2     | 2     | 3     | 
------------------------------
| 3  | 3     | 3     | 3     | 
------------------------------
| 4  | 3     | 3     | 3     | 
------------------------------

second table:
------------------------------
| id | test1 | test2 | test3 | 
------------------------------
| 1  | 1     | 1     | 1     | 
------------------------------
| 2  | 2     | 2     | 2     | 
------------------------------
| 3  | 3     | 3     | 3     | 
------------------------------
| 4  | 3     | 3     | 3     | 
-------------------------------
| 5  | 3     | 9     | 3     |
------------------------------

So the result would be:

------------------------------
| id | test1 | test2 | test3 | 
------------------------------
| 2  | 2     | 2     | *2*   |
------------------------------
| 2  | 2     | 2     | *3*   |
------------------------------
| 5  | 3     | *9*   | 3     | 
------------------------------

The code to do this for now which I got is:

SELECT dbo.first.[test1], dbo.first.[test2], dbo.first.[test3],
dbo.second.[test1], dbo.second.[test2], dbo.second.[test3] 
FROM dbo.first left join dbo.second on 
dbo.first.[test1]=dbo.second.[test1] and 
dbo.first.[test2]=dbo.second.[test2] and 
dbo.first.[test3]=dbo.second.[test3] 

But this isnt showing the correct result.

Thanks in advance for helping.

Answer 1

Because you are dealing with all columns, except or minus is the simplest way. It looks like you are using SQL Server, so:

select *
from (select t1.* from t1
      except
      select t2.* from t2
     ) tt
union all
select *
from (select t2.* from t2
      except
      select t1.* from t1
     ) tt