SQLSERVER2005 In语句的问题

Question

select * From Empoylee Where
(Address1,Address2) in 
      (Select Address1,Address2
             From EmpoyleeAdresses 
             Where Country = 'Canada')
可以实现吗，有没有什么替代方法

Answer 1

用exists判断
select a.* From Empoylee a
Where exists(select 1 from EmpoyleeAdresses b Where b.Country = 'Canada' and a.Address1=b.Address1 and a.Address2=b.Address2)

Answer 2

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

Answer 3

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

Answer 4

select Empoylee.* From Empoylee,
(Select Address1,Address2
From EmpoyleeAdresses
Where Country = 'Canada') t
where Empoylee.Address1=t.address1 and Empoylee.Address2=t.address2

Answer 5

我用的就是Inner join
可是好像很慢

Answer 6

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

Answer 7

实践证明：当EmpoyleeAdresses表数据很多时exists快一些，不知道什么原因