18

Looking for the easist/most scalable way to do a set "difference" in SQL Server see below. alt text

If you can't tell from the picture i am looking for everything that is not in the intersection.

I have seen one way to do it:

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 fear what would happen if i used two large sets (i will not be querying from select '' constant statements, my queries will be pulling 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 int dog

Community
  • 1
  • 1
Nix
  • 57,072
  • 29
  • 149
  • 198

3 Answers3

24

How about something like this?

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 nullable 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
JohnFx
  • 34,542
  • 18
  • 104
  • 162
  • What does the IsNull part achieve? It it just a means to "show" what is missing? – Nix Jul 16 '10 at 12:40
  • 1
    For rows that only have a record in one of the tables (the ones you want) there will be a null in one table or the other. For example either Table1.A will be null or Table2.A will be null for each row. The Isnull grabs the value from whichever side has a value. – JohnFx Jul 16 '10 at 14:01
  • Question is about SQL Server, but comes up for general SQL searches. Note: IsNull(a,b) is Sql Server specific. The generic SQL equivalent is COALESCE(), and by the way, coalesce is actually more robust than IsNull since IsNull performs a replacement operation on a single argument. Coalesce() takes any number of parameters and grabs the first non-null parameter. Highly recommend coalesce always instead of IsNull – JamesHoux Jun 12 '20 at 16:49
  • Also, IsNull is poorly named and not human-intuitive, since it sounds like it only does a simple True/False test with return instead of an optional replacement operation. – JamesHoux Jun 13 '20 at 01:51
14

Alternative:

SELECT A, B FROM Table1 UNION SELECT A,B FROM Table2
EXCEPT
SELECT A, B FROM Table2 INTERSECT SELECT A,B FROM Table1
Gumowy Kaczak
  • 1,457
  • 2
  • 16
  • 28
  • +1 I see a user-defined function coming out of this... `with A as ( select A, B from A ), B as ( select A, B from B ) select * from A union select * from B except select * from A intersect select * from B;` – Zachary Ryan Smith Jan 30 '19 at 21:53
4

What you're after is called a Full Outer Join, which SQL Server supports.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • I initially thought something like that: select * from (select 'test1' as a, 1 as b) One FULL JOIN (select 'test2' as a , 2 as b union all select 'test1' as a , 1 as b ) Two ON One.a <> Two.a AND One.b <> Two.b However it's not (run both SQL) – Joel Mansford Jul 15 '10 at 22:02
  • 1
    yes, but you also have to filter out all the matches after the join: `WHERE keyA is NULL OR keyB is NULL` – Remus Rusanu Jul 15 '10 at 22:10
  • 8
    A full outer join is NOT what the question is asking for. A full outer join would be the Venn Diagram completely filled in, not just the non-intersecting portions of the Venn Diagram. – Dogs Mar 21 '17 at 13:06