>First is more readable, but only because you set it up that way ;-)

>While the optimizer probably will create identical plans, for me only link fields belong into join-on clause, with a
>where POH.OrderDate BETWEEN '1-1-2008' AND '12-31-2008'

Really? I've been putting additional conditions on a left join for 20 years now. Its a second nature to me. You can do really beautiful things with it which you cannot achieve as easily in any other way.

As for the execution plan. I'm not quite sure about that. If there is an compound index on vendorID, orderdate, TotalDue, the left join will reach full index coverage, as where I suspect the CTE will materialize first. But did not try this as I do not have that database installed.

>BTW:
>> group by Vend.Name
>might result in different results from
>> GROUP BY VendorID)

Correct, it should be done on a unique ID.

>;-)
>
>
>>>Through i recognise the place of CTE's , I really hate to use them, and would avoid them whenever possible. One of the problems is that it is not an ANSI standard and often there is a better (readable and maintainable) alternative.
>>>
>>>Interesting. I really like CTEs and I think that, in many instances, replacing derived tables with them makes the code much more readable and maintainable.
>>
>>
>>Do you think that:
>>
>>

>>   ;WITH POTemp as
>>      ( SELECT VendorID, SUM(TotalDue) as VendorTotal 
>>                FROM Purchasing.PurchaseOrderHeader
>>                WHERE	 where OrderDate BETWEEN '1-1-2008' AND '12-31-2008'  
>>                  GROUP BY VendorID) 
>>   
>>  select Vend.Name,  VendorTotal
>>    from  Purchasing.Vendor   as Vend
>>      left outer   join POTemp
>>               on Vend.BusinessEntityID = POTemp.VendorID
>>      order by VendorTotal desc
>>

>>
>>Is more readable than
>>
>>

>>select Vend.Name, SUM(TotalDue) as VendorTotal
>>      from  Purchasing.Vendor   as Vend
>>         left outer  join  Purchasing.PurchaseOrderHeader AS POH  
>>               on Vend.BusinessEntityID = POH.VendorID AND POH.OrderDate BETWEEN '1-1-2008' AND '12-31-2008'   
>>         group by Vend.Name   
>>         order by VendorTotal desc
>>

>>
>>or
>>
>>

>>select Vend.Name, (SELECT SUM(TotalDue) FROM   Purchasing.PurchaseOrderHeader 
>>     WHEREVend.BusinessEntityID = VendorID AND OrderDate BETWEEN '1-1-2008' AND '12-31-2008') as VendorTotal
>>      from  Purchasing.Vendor   as Vend
>>      order by VendorTotal desc
>>

>>?
>>
>>As I was saying, in an awfull lot of case you just do not need CTE's as there is a better alternative.
>>
>>Aside from that, CTE's cannot be used as a total replacement of temp tables which are dirived from more complex statement as you cannot nest CTE's and cannot use two CTE's in one SQL statement.
>>
>>In my carreer I've used CTE's only in a handful of cases, but only as a last resort. Esspecially the recursive variant is sometimes very difficult to follow and therefore hardly maintainable. I appreciate people use it to show their capabilities, but to me, simplicity and maintainability is worth much more than to score with a SQL statement that can only be analysed and maintained by high skilled SQL programmers.