select * From Empoylee Where
(Address1,Address2) in
(Select Address1,Address2
From EmpoyleeAdresses
Where Country = 'Canada')
以上无法实现(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内链接Where exists
(select 'A' from EmpoyleeAdresses b
Where
b.Country = 'Canada'
and a.Address1=b.Address1
and a.Address2=b.Address2
)
select *
From
Empoylee
inner join EmpoyleeAdresses
on
Empoylee.Address1=EmpoyleeAdresses.Address1
and
Empoylee.Address2=EmpoyleeAdresses.Address2
Where
EmpoyleeAdresses.Country = 'Canada')
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:表关联from
Empoylee em
inner join EmpoyleeAdresses emA
on
em.Address1=emA.Address1
and
em.Address2=emA.Address2
and
emA.Country = 'Canada'
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更快一些,客观上,还没在查询分析器里用"显示执行计划"看哪种写法效率高些.
From
Empoylee a ,
(Select
Address1,Address2 From EmpoyleeAdresses
Where
Country = 'Canada') b
Where
a.Address1=b.Address1
and
a.Address2 = b.Address2