Posted by Erland Sommarskog on 07/02/07 21:45

Gert-Jan Strik (sorry@toomuchspamalready.nl) writes:
> If you specify Left Join instead of Inner Join, you are basically doing
> two things:
> 1. you are reducing the number of potential access paths during
> compilation
> 2. you are 'forcing' the access path between the two tables: from the
> outer table to the inner table

Maybe. If the condition is over trusted non-nullable FK, the optimizer
should look through the trick. For instance:

select OD.*
from Northwind..[Order Details] OD
left join Northwind..Orders O ON OD.OrderID = O.OrderID
WHERE O.CustomerID = 'VINET'

Starts with accessing the Orders table.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Dynamic Query
• Prev in forum: Re: SQL Query question
• Thread view: Re: Performance between Standard Join and Inner Join

[Reply to this message]