SQL Server差异(与intersect相反)

Looking for the easist/most scalable way to do a set "difference" in SQL Server see below. SQL Server差异(与intersect相反)

在SQL Server中查找最容易/最可伸缩的方式来设置“差异”，如下所示。

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.)

但是我担心如果我使用两个大的集合会发生什么(我不会从select“constant语句中查询，我的查询将从真实的表中提取)。

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

1弦int紫色

3 dog int cat

3狗int猫

1 String dec purple

12月1串紫色

3 dog int dog

3狗int狗

3 个解决方案

#1

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)

下面是一个完整的外部连接方法的示例(假设两个表中的A不可为空)

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

#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

#3

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

您所追求的是一个完整的外部连接，SQL Server支持它。

#1