各位大侠,帮帮忙啊,小弟迷惑了
如下脚本:
set statistics profile on
SELECT
d.*,
u.*
FROM
subsidiaryreportsystem.dbo.distributorstaffmapping d WITH (NOLOCK)
LEFT outer JOIN subsidiaryreportsystem.dbo.tbluser u WITH (NOLOCK)
ON
d.userid = u.userid
WHERE
u.admin = 1
AND d.staffid = 50
AND u.province = '上海'
set statistics profile off
执行计划如下:
请注意上图中标示的部分,为什么是inner join 呢?我明明是left outer join啊
然后,我把上面相同的语句,去掉where条件,执行:
set statistics profile on
SELECT
d.*,
u.*
FROM
subsidiaryreportsystem.dbo.distributorstaffmapping d WITH (NOLOCK)
LEFT outer JOIN subsidiaryreportsystem.dbo.tbluser u WITH (NOLOCK)
ON
d.userid = u.userid
set statistics profile off
执行计划如下:
请注意上图中标示的部分,变成了正常的left outer join 。。。
这一切到底是为什么呀?
请大神给解释下,特别是left join的执行过程,以及带where条件的left join和不带where条件的left join,他们之间的区别和执行机理,感谢了。
------解决方案--------------------SELECT
d.*,
u.*
FROM
subsidiaryreportsystem.dbo.distributorstaffmapping d WITH (NOLOCK)
LEFT outer JOIN subsidiaryreportsystem.dbo.tbluser u WITH (NOLOCK)
ON
d.userid = u.userid
WHERE
u.admin = 1
AND d.staffid = 50
AND u.province = '上海'
你left join u表 就不要在where里限制u了 不然就等于inner了
可以如此:
SELECT
d.*,
u.*
FROM
subsidiaryreportsystem.dbo.distributorstaffmapping d WITH (NOLOCK)
LEFT outer JOIN subsidiaryreportsystem.dbo.tbluser u WITH (NOLOCK)
ON
d.userid = u.userid AND u.admin = 1 AND u.province = '上海'
WHERE d.staffid = 50
------解决方案--------------------带where的外联结,实际上是先inner join,把复合条件的数据查出来,然后再补上左/右/两边表的未匹配数据