怎样Sql语句中避免使用IN或者NOT IN?

[zlmark]

我在SQL Best Practices中看到要尽量避免使用In或者Not in, 因为这样index不起作用,可是在实际写程序时碰到条件筛选语句,如下条语句:
select * from Region where regionid in (Select regionid from Sales_Region where userid=@UserID)
or regionid in (Select regionid from Region_RegionOffice where roid in (Select roid from Sales_RO where userid=@UserID))

这样的sql语句应该怎样改写呢?
可能我写的语句不太好,影响数据库性能,请大家指教.

[AntingZ]

select * from Region where regionid in (Select regionid from Sales_Region where userid=@UserID)

可以换成：

select * from Region a where exists (Select 1 from Sales_Region b where userid=@UserID and b.regionid = a.regionid )

[yesterday2000]

select a.* from Region a,Sales_Region b regionid c,Region_RegionOffice d where (a.regionid=b.regionid and b.userid=@UserID)
or (a.regionid=c.regionid and c.roid=roid and d.userid=@userid)

[zjcxc]

declare @UserID int
select * from Region a
where exists(
Select regionid from Sales_Region
where userid=@UserID
and regionid=a.regionid)
or exists(
Select regionid from Region_RegionOffice bb
where regionid=a.regionid
and exists(
Select roid from Sales_RO
where userid=@UserID
and roid=bb.regionid))

[AntingZ]

这样改的话，感觉性能不会提高。
还是听高手来讲。wait

[zjcxc]

--或者:
select a.* from Region a,(
Select regionid from Sales_Region
where userid=@UserID
union
Select regionid from Region_RegionOffice bb
where regionid=a.regionid
and exists(
Select roid from Sales_RO
where userid=@UserID
and roid=bb.regionid)
)b where a.regionid=b.regionid

[zjcxc]

--上面错了,是这样:
select a.* from Region a,(
Select regionid from Sales_Region
where userid=@UserID
union
Select regionid from Region_RegionOffice bb
where exists(
Select roid from Sales_RO
where userid=@UserID
and roid=bb.regionid)
)b where a.regionid=b.regionid

[AntingZ]

我才打了两行字，高手插到前面去了

[LoveSQL]

select a.* from Region a ,Sales_Region b where a.regionid=b.regionid and b.userid=@UserID

union all

select a.* from Region a,Region_RegionOffice b,Sales_RO c where a.regionid=b.regionid and b.roid=c.roid and c.userid=@UserID

[rouqu]

试试
select * from Region R where
exists (select regionid from Sales_Region where userid = @userID
and regionid = R.gegionid)
or
exists (select rr.regionid from Region_RegionOffice rr,Sales_RO s
where s.userid = @userID and rr.roid = s.roid)

[rouqu]

select * from Region R where
exists (select regionid from Sales_Region where userid = @userID
and regionid = R.regionid)
or
exists (select rr.regionid from Region_RegionOffice rr,Sales_RO s
where s.userid = @userID and rr.roid = s.roid and rr.regionid = R.regionid)