Help for Union/Intersect query

[nandini]

There are two tables with identical structures.
I want a single query (TSQL) to obtain the rows which are not common between the tables. Please note that the difference may be in any column.
i.e. in set theory notation I want
(Tab1-Tab2)U(Tab2-Tab1)

Please help !!

 

[CatParks]

Try UNION ALL between select statements. I used this the other day in a view to accomplish something similar. It would look something like this:

SELECT a,b,c
FROM table

UNION ALL

SELECT a,b,c
FROM table

Remember: (1) The number and the order of the columns must be identical in all queries. (2) The data types must be compatible. X-)

 

[sguslan]

Note: the only requirement in a Union is that respective columns in the union queries are of the same data type.

So,

select b,c,f from tab1
union
select f,d,h from tab2
union f1,f4,f6 from tab3

will work, so long as column 1 of all selects has the same data type and of course the same goes for other columns.

 

[sguslan]

Note: the only requirement in a Union is that respective columns in the union queries are of the same data type.

So,

select b,c,f from tab1
union
select f,d,h from tab2
union
select f1,f4,f6 from tab3

will work, so long as column 1 of all selects has the same data type and of course the same goes for other columns.

 

[nandini]

The SQL in the solutions given to my query will fetch all the rows or all the distinct rows in the two tables.

My problem was to obtain the rows which are NOT COMMON between the tables.
e.g. There are two tables say emp and emp1 having columns eno, ename

The rows in emp :
1 emp1
2 emp2
3 emp3

The rows in emp1 :
1 emp4
2 emp2
3 emp5

I want the SQL to retun the following rows
1 emp1
3 emp3
1 emp4
3 emp5