Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Difference (opposite of intersect)

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

like image 489
Nix Avatar asked Jul 15 '10 21:07

Nix


People also ask

What is opposite of SQL INTERSECT?

The UNION, EXCEPT and INTERSECT operators of SQL enable you to combine more than one SELECT statement to form a single result set. The UNION operator returns all rows. The INTERSECT operator returns all rows that are in both result sets.

What is the difference between INTERSECT and except in SQL Server?

EXCEPT returns distinct rows from the left input query that aren't output by the right input query. INTERSECT returns distinct rows that are output by both the left and right input queries operator.

What is difference between INTERSECT and MINUS in SQL?

INTERSECT compares the data between tables and returns only the rows of data that exist in both tables. MINUS compares the data between tables and returns the rows of data that exist only in the first table you specify.


3 Answers

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
like image 152
JohnFx Avatar answered Oct 16 '22 13:10

JohnFx


Alternative:

SELECT A, B FROM Table1 UNION SELECT A,B FROM Table2
EXCEPT
SELECT A, B FROM Table2 INTERSECT SELECT A,B FROM Table1
like image 45
Gumowy Kaczak Avatar answered Oct 16 '22 14:10

Gumowy Kaczak


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

like image 5
OMG Ponies Avatar answered Oct 16 '22 14:10

OMG Ponies