SQL left join 效率问题

[castlooo]

--SQL1
SELECT * FROM A LEFT JOIN B  ON A.ID = B.ID
WHERE B.ID>100 AND A.ID>100

--SQL2
SELECT * FROM A
LEFT JOIN 
( SELECT * FROM B WHERE B.ID>100) TB
ON A.ID = TB.ID
WHERE A.ID>100

SQL1 和SQL2那个效率高些？ 为什么？

[Rewiah]

先不说效率，结果是不同的

[xieyueqing]

自己测试一下吧,有些东西没准的,SQL 的优化器还是不错的

[等不到来世]

我原来做过一定的测试，貌似两个效率相近。
应该跟数据量和数据分布情况等具体环境有关系。
单从写法上来说，推荐第1种。

[Rewiah]

如果A有个id>100的记录，比如id=101，而b中没有
SQL1不会出现这条记录
SQL2会出现这条记录，其中b.id=null


结果不同的语句比较效率是没有意义的

[xieyueqing]

[Quote=引用 5 楼 Rewiah 的回复:]
先不说效率，结果是不同的
[/Quote]
结果应该是相同的阿。。。

[Rewiah]

正常写法应该

--SQL1
SELECT * FROM A LEFT JOIN B  ON A.ID = B.ID
WHERE A.ID>100

这样效果就一样了
效率应该是这个最好

[jackyren007]

感觉B快点.

B有个:SELECT * FROM B WHERE B.ID>100

就已经过滤掉一些数据了.

最后再做的LEFT JOIN

[dobear_0922]

--SQL1 
SELECT * FROM A LEFT JOIN B  ON A.ID = B.ID 
WHERE B.ID>100 AND A.ID>100 
--相当于：
SELECT * FROM A JOIN B  ON A.ID = B.ID 
WHERE B.ID>100 AND A.ID>100 

因为你的筛选条件中包含B表中的列，优化器会将外left outer join 改为inner join

[jackyren007]

还是放到查询规划器,看下,是怎么规划的就知道了.

[dobear_0922]

[Quote=引用 12 楼 dobear_0922 的回复:]
SQL code--SQL1
SELECT * FROM A LEFT JOIN B ON A.ID = B.ID
WHERE B.ID>100 AND A.ID>100
--相当于：
SELECT * FROM A JOIN B ON A.ID = B.ID
WHERE B.ID>100 AND A.ID>100



因为你的筛选条件中包含B表中的列，优化器会将外left outer join 改为inner join
[/Quote]

SQL1与SQL2产生的结果有可能会不同（SQL1的结果集中没有B表字段为NULL的行），当然只比较执行时间的话，SQL1会快一些。

[Rewiah]

[Quote=引用 9 楼 xieyueqing 的回复:]
引用 5 楼 Rewiah 的回复:
先不说效率，结果是不同的

结果应该是相同的阿。。。
[/Quote]

这个不用争论，测试下就知道了

create table #a (
id int)

go
create table #b (
id int)

go

insert #a select 101 union all select 102

insert #b select 102 union all select 103

--SQL1
SELECT * FROM #a A LEFT JOIN #b B  ON A.ID = B.ID
WHERE B.ID>100 AND A.ID>100

--SQL2
SELECT * FROM #a A
LEFT JOIN 
( SELECT * FROM #b B WHERE B.ID>100) TB
ON A.ID = TB.ID
WHERE A.ID>100

--SQL3
SELECT * FROM #a A LEFT JOIN #b B  ON A.ID = B.ID
WHERE A.ID>100

--结果
id          id
----------- -----------
102         102

(1 行受影响)

id          id
----------- -----------
101         NULL
102         102

(2 行受影响)

id          id
----------- -----------
101         NULL
102         102

(2 行受影响)

[等不到来世]

[Quote=引用 8 楼 Rewiah 的回复:]
如果A有个id>100的记录，比如id=101，而b中没有
SQL1不会出现这条记录
SQL2会出现这条记录，其中b.id=null


结果不同的语句比较效率是没有意义的
[/Quote]
果然经验丰富！

[等不到来世]

[Quote=引用 14 楼 dobear_0922 的回复:]
引用 12 楼 dobear_0922 的回复:
SQL code--SQL1
SELECT * FROM A LEFT JOIN B ON A.ID = B.ID
WHERE B.ID>100 AND A.ID>100
--相当于：
SELECT * FROM A JOIN B ON A.ID = B.ID
WHERE B.ID>100 AND A.ID>100


因为你的筛选条件中包含B表中的列，优化器会将外left outer join 改为inner join


SQL1与SQL2产生的结果有可能会不同（SQL1的结果集中没有B表字段为NULL的行），当然只比较执行时间的话，SQL1会…
[/Quote]
我买新电脑了，以后估计把上csdn的时间用来打魔兽了！hoho

[wanshichen]

从写法上推荐第一种，比较正统的高效写法……

[bluefangxiao]

up

[bhtfg538]

[Quote=引用楼主 castlooo 的帖子:]
SQL code
--SQL1
SELECT * FROM A LEFT JOIN B ON A.ID = B.ID
WHERE B.ID>100 AND A.ID>100

--SQL2
SELECT * FROM A
LEFT JOIN
( SELECT * FROM B WHERE B.ID>100) TB
ON A.ID = TB.ID
WHERE A.ID>100



SQL1 和SQL2那个效率高些？ 为什么？
[/Quote]
LZ 可以去看看 我的 www.cnblogs.com/bhtfg538

[bhtfg538]

看看他们的 执行 顺训
就知道他们的 效率 --SQL1
SELECT * FROM A LEFT JOIN B ON A.ID = B.ID
WHERE B.ID>100 AND A.ID>100
我没开SMSS
物理顺序大概是 先从FROM 表找到 A 发现 有 where 过滤 a 的记录
然后找到 b 同样过滤 B.ID>100
然后 执行 LEFT JOIN 把a 表的 记录重新加进去
再执行ON 连接
--SQL2
SELECT * FROM A
LEFT JOIN
( SELECT * FROM B WHERE B.ID>100) TB
ON A.ID = TB.ID
WHERE A.ID>100

这个的话 基本是这样的
( SELECT * FROM B WHERE B.ID>100) TB
这个 基本 和 SQL1差不多
MS SQL SERVER 2005 应该是的

[MikeCheers]

应该这样写比较高效
SELECT * FROM
( SELECT * FROM A WHERE A.ID > 100) TA
LEFT JOIN
( SELECT * FROM B WHERE B.ID > 100) TB
ON TA.ID = TB.ID

最起码left join应该尽量减少左表大小, 才是提升效率的根本.

[mikkk]

来学习大家的心得。

[gblovezx]

有没有人真正的试过呢