TSQL中实现ORACLE的多列IN

期望效果：

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt select   *   From  Empoylee  Where
(Address1,Address2)  in
( Select  Address1,Address2
From  EmpoyleeAdresses
Where  Country  =   ' Canada ' )

以上无法实现
解决方案1：用exists判断

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt select  a. *   From  Empoylee a
Where   exists
    ( select   ' A '   from  EmpoyleeAdresses b
      Where
        b.Country  =   ' Canada '
             and  a.Address1 = b.Address1
             and  a.Address2 = b.Address2
        )

解决方案2：用inner join内链接

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt select   *
From
Empoylee
inner   join  EmpoyleeAdresses
on
Empoylee.Address1 = EmpoyleeAdresses.Address1
and
Empoylee.Address2 = EmpoyleeAdresses.Address2
Where
EmpoyleeAdresses.Country  =   ' Canada ' )

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt select   *  
from
Empoylee em
inner   join  EmpoyleeAdresses emA
on
em.Address1 = emA.Address1
and
em.Address2 = emA.Address2
and
emA.Country  =   ' Canada '

解决方案3：表关联

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt select  a. *
From  
Empoylee a ,
  ( Select
  Address1,Address2  From  EmpoyleeAdresses 
   Where
  Country  =   ' Canada ' ) b
Where
a.Address1 = b.Address1
and
a.Address2  =  b.Address2

主观感觉方案1更快一些，客观上，还没在查询分析器里用"显示执行计划"看哪种写法效率高些.

如果是Delete语句，写法如下：

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt Delete From  Empoylee 
Where   exists
    ( select   ' A '   from  EmpoyleeAdresses b
      Where
        b.Country  =   ' Canada '
             and  Empoylee.Address1 = b.Address1
             and  Empoylee.Address2 = b.Address2
        )

注意Delete from 后的表不能用别名～

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/216599/viewspace-553502/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/216599/viewspace-553502/