Posted by Tom Moreau on 05/18/06 11:40

Be careful. Your LEFT JOIN is actually an INNER JOIN in disguise (as well
as having a syntax error). Try:

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.item1 = TableB.item1
AND TableB.item2 > yyyy AND TableB.item2 < zzzz
WHERE TableA.item2 = 'xxxx'

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada

<lelandhuang@gmail.com> wrote in message
news:1147934223.710966.324990@j33g2000cwa.googlegroups.com...
I am developing reporting service and using lots of 'LEFT OUTER JOIN',
I am worried about the performance and want to use some subquery to
improve
the performance.
Could I do that like below,

[the origin source]
SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.item1 = TableB.item1
WHERE TableA.item2 = 'xxxx'
TableB.item2 > yyyy AND TableB.item2 < zzzz

I add the subquery to query every table before 'LEFT JOIN'
--------------------------------------------------------------------------
SELECT *
FROM
(SELECT *
FROM TableA
WHERE TableA.item2 = 'xxxx'
) TableC
LEFT OUTER JOIN
(SELECT *
FROM TableB
WHERE TableB.item2 > yyyy AND TableB.item2 < zzzz
) TableD
ON TableC.item1 = TableD.item1
WHERE TableC.item2 = 'xxxx'
TableD.item2 > yyyy AND TableD.item2 < zzzz
--------------------------------------------------------------------------

Can anyone give me some suggestion?
Thanks a lot.

Leland Huang

• Next in forum: Re: RAID 5 beats RAID 10
• Prev in forum: Copying files between servers
• Thread view: Re: how to improve performance of 'LEFT JOIN'

[Reply to this message]