这样效率应该会高些:select * from (
select *
from table1
where table1.b2=@str
) tab1 left join (select * from table2 where b2=@str) as tab2
on tab1.c1 = tab2.c2
select *
from table1
where table1.b2=@str
) tab1 left join (select * from table2 where b2=@str) as tab2
on tab1.c1 = tab2.c2
declare @str char(10)
set @str='1234567890'
select * from table1 left join table2
on table1.c1 = tab2.c2
where table2.b2=@str
效率高了,结果不一样了Yang_(扬帆破浪)
我试了一下,好象效率没有提高
on table1.c1 = tab2.c2
where table2.b2=@str and table1.b2=@str
from (select * from table1 where table1.b2 = @str) as a
left join table2 as b
on a.c1 = b.c2 and a.b1 = b.b2
on table1.c1 = table2.c2 and table1.b2 = table2.b2
where table1.b2 = @str
/*
create table tb1(a1 varchar(50),b1 int,c1 int)
create table tb2(a2 varchar(50),b2 int,c2 int)
--select * from tb1
--select * from tb2
insert into tb1 values('123',2,0)
insert into tb1 values('124',2,1)
insert into tb1 values('125',3,2)
insert into tb2 values('224',2,1)
insert into tb2 values('225',3,2)
*/
declare @b int
set @b=2/*low*/
select *
from tb1 left join (select * from tb2 where b2=@b) as bb2 on tb1.c1=bb2.c2
where
tb1.b1=@b/*fast but error*/
select *
from tb1 left join tb2 on tb1.c1=tb2.c2
where
tb1.b1=@b and tb2.b2=@b/*low*/
select *
from (select * from tb1 where b1=@b) as bb1 left join tb2 on bb1.c1=tb2.c2 and bb1.b1=tb2.b2
对两个表的B字段加索引能提高效率。
最好对(B,C)加索引。
set @str='1234567890'
select A.* from table1 A left join table2 B
on A.c1 = B.c2 AND B.b2=@str这样的效率会高一些,一位SQL Server做计划的时候制作了一个查询计划,用字查询的话,SQL Server会先做子查询的计划,在做外部查询的计划。
另外 在左联接的时候,针对右面的表的条件要放到ON 的后面,这样才不会出错,不让放到WHERE 里有时会出现查不出记录的情况。
应该是这样
这样的效率会高一些,因为SQL Server做计划的时候只作了一个查询计划;
用子查询的话,SQL Server会先做子查询的计划,在做外部查询的计划。另外 在左联接的时候,针对右面的表的条件要放到ON 的后面,这样才不会出错,不然如果放到WHERE 里有时会出现查不出记录的情况。
总该要的吧