期望效果:
select
*
From
Empoylee
Where
(Address1,Address2)
in
(
Select
Address1,Address2
From
EmpoyleeAdresses
Where
Country
=
'
Canada
'
)
以上无法实现
解决方案1:用exists判断
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内链接
select
*
From
Empoylee
inner
join
EmpoyleeAdresses
on
Empoylee.Address1
=
EmpoyleeAdresses.Address1
and
Empoylee.Address2
=
EmpoyleeAdresses.Address2
Where
EmpoyleeAdresses.Country
=
'
Canada
'
)
select
*
from
Empoylee em
inner
join
EmpoyleeAdresses emA
on
em.Address1
=
emA.Address1
and
em.Address2
=
emA.Address2
and
emA.Country
=
'
Canada
'
解决方案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
主观感觉方案1更快一些,客观上,还没在查询分析器里用"显示执行计划"看哪种写法效率高些.