SQL SERVER- Differences Between Left Join and Left Outer Join

There are a few questions that I had decided not to discuss on this blog because I think they are very simple and many of us know it. Many times, I even receive not-so positive notes from several readers when I am writing something simple. However, assuming that we know all and beginners should know everything is not the right attitude.

Since day 1, I have been keeping a small journal regarding questions that I receive in this blog. There are around 200+ questions I receive every day through emails, comments and occasional phone calls. Yesterday, I received a comment with the following question:

This question has triggered the threshold of receiving the same question repeatedly. Here is the answer:

There is absolutely no difference between LEFT JOIN and LEFT OUTER JOIN. The same is true for RIGHT JOIN and RIGHT OUTER JOIN. When you use LEFT JOIN keyword in SQL Server, it means LEFT OUTER JOIN only.

I have already written in-depth visual diagram discussing the JOINs. I encourage all of you to read the article for further understanding of the JOINs:

Theta joins are made up by the interviewer. This is used by many interviewers to make sure you know fact from fiction. You can’t believe everything you read on the net, and this applies to SQL big time!

Hi Pinal,
I just came across the following scenario today and wanted to get it clarified from you. I know there is no difference between left and left outer join but however the result set was different in my scenario. Bascially I joined two tables using left outer join (note my select query had isnull in it) result I got was no rows but when I removed the outer from it (used only left join) I got 1 rows with column values as 0. Why is this difference

SELECT * FROM (SELECT DISTINCT [Table1].* , ROW_NUMBER() OVER (ORDER BY [Table1].Name) AS RowNumber
FROM [Table1]
LEFT JOIN [Table2] ON [Table1].Id = [Table2].Id) AS TempTable

SELECT * FROM (SELECT DISTINCT [Table1].* , ROW_NUMBER() OVER (ORDER BY [Table1].Name) AS RowNumber
FROM [Table1]
LEFT JOIN [Table2] ON [Table1].Id = [Table2].Id) AS TempTable
WHERE RowNumber BETWEEN 1 AND 10

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.