LEFT JOIN and LEFT OUTER JOIN speed difference

Hi Experts,
The query below runs much faster if I replace LEFT JOIN with LEFT OUTER JOIN. Both joins return the same data, which is all rows from the table on the left and matching rows from the table on the right.

Besides performance in terms of speed, what should I consider when using LEFT JOIN and LEFT OUTER JOIN?

Thanks
SELECT * FROM 
(SELECT Authors.ID, (Authors.LastName + '', '' + Authors.FirstName) as Author, Authors.Notes,
AuthorHeadings.HeadingName AS HeadingType, COUNT(Books_Authors.BookID) AS TitleCount, 
ROW_NUMBER() OVER (ORDER BY LastName) AS RowRank 
FROM Authors 
INNER JOIN AuthorHeadings ON Authors.HeadingID = AuthorHeadings.ID 
LEFT JOIN Books_Authors ON Authors.ID = Books_Authors.AuthorID 
WHERE (Authors.FirstName LIKE %Ja%)
GROUP BY Authors.ID, Authors.LastName, Authors.FirstName, AuthorHeadings.HeadingName, Authors.Notes) 
AS AuthorsWithRows
WHERE (RowRank >0) AND (RowRank <= 500)

Open in new window

noobe1Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
there is no difference, LEfT JOIN and LEFT OUTER JOIN are synonyms
0
 
YiogiConnect With a Mentor Commented:
They are exactly the same. It shouldn't have a speed difference actually. Perhaps you run LEFT JOIN first and then LEFT OUTER JOIN and the execution plan was already cached so you got a faster result. Try each a few times and it should take exactly the same time to return data.
0
 
i2mentalConnect With a Mentor Commented:
It probably ran faster because you ran it the first time as LEFT JOIN, the query was cached, you changed it to LEFT OUTER JOIN and it uses the same execution plan so therefor ran off the cached plan and was faster. I bet if you run it with LEFT JOIN once more, it will be faster as well. They mean the same thing as AngelIII said.
0
 
dportasConnect With a Mentor Commented:
Take a look at the execution plans in each case - they should be the same. SET STATISTICS IO ON to convince yourself that the same pages are being read in each case.

Note that there are syntax errors in your code so I don't think you posted what you actually ran.
0
All Courses

From novice to tech pro — start learning today.