SQL Server difference (opposite the intersection)

Question

Looking for the easiest / most scalable way to make a “difference” in SQL Server, see below.

If you can’t tell from the picture, I’m looking for everything that is not at the intersection.

I saw one way to do this:

select * from ( (select 'test1' as a, 1 as b) union all (select 'test2' as a , 2 as b union all select 'test1' as a , 1 as b ) )un group by a,b having count(1)=1 

But I'm afraid what will happen if I use two large sets (I won’t ask from select '' constant operators, my queries will be pulled from real tables.)

EDIT:

Possible Solution...

 drop table #temp_a; drop table #temp_b; go select * into #temp_a from ( select 1 as num, 'String' as two, 'int'as three, 'purple' as four union all select 2 as num, 'dog' as two, 'int'as three, 'purple' as four union all select 3 as num, 'dog' as two, 'int'as three, 'cat' as four ) a select * into #temp_b from ( select 1 as num, 'String' as two, 'decimal'as three, 'purple' as four union all select 2 as num, 'dog' as two, 'int'as three, 'purple' as four union all select 3 as num, 'dog' as two, 'int'as three, 'dog' as four ) b SELECT IsNull(a.num, b.num) A,IsNull(a.two, b.two) B, IsNull(a.three, b.three) C, IsNull(a.four, b.four) D FROM #temp_a a FULL OUTER JOIN #temp_b b ON (a.num=b.num AND a.two=b.two and a.three=b.three and a.four=b.four) WHERE (a.num is null or b.num is null ) 

RESULTS:

1 String int purple

3 dog int cat

1 String dec purple

3 dog dogs

Answer 1

How about something like that?

 SELECT A, B FROM Table1 EXCEPT SELECT A,B FROM Table2 UNION SELECT A, B FROM Table2 EXCEPT SELECT A,B FROM Table1 

Here is an example with the FULL OUTER JOIN method (assuming A is not NULL in both tables)

 SELECT IsNull(Table1.A, Table2.A) a,IsNull(Table1.B, Table2.B) B FROM Table1 FULL OUTER JOIN Table2 ON (Table1.A=Table2.A AND Table1.B=Table2.B) WHERE Table1.A is null or Table2.A is null 

Answer 2

Alternative:

 SELECT A, B FROM Table1 UNION SELECT A,B FROM Table2 EXCEPT SELECT A, B FROM Table2 INTERSECT SELECT A,B FROM Table1 

Answer 3

What you need is called Full Outer Join, which supports SQL Server .