LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

Question

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

Answer 1

I know that this is old however wanted to put my two cents in. I understand that the LEFT JOIN should be the same as LEFT OUTER JOIN but in my experience I have seen a LEFT JOIN pull back different Results than a LEFT OUTER JOIN so I have started to use the key word OUTER to be more specific and proper. Rows that should have come back in a LEFT JOIN did not where as when I would use a LEFT OUTER JOIN it did. I was trying to explain this to a colleague when he was unable to get the rows that he needed as well so I decided to Google the difference so as to have some sort of backing to show him. This might be a SQL Server specific thing to which I am uncertain about. I would say that in good practice it would be more advisable to explicitly state that you want an outer join to occur. Just my opinion.

Answer 2

LEFT JOIN performs a join starting with the first (left-most) table and then any matching second (right-most) table records.

LEFT JOIN and LEFT OUTER JOIN are the same.

According to DoFactory

Answer 3

To answer your question there is no difference between LEFT JOIN and LEFT OUTER JOIN, they are exactly same that said...

At the top level there are mainly 3 types of joins:

1. INNER
2. OUTER
3. CROSS

---

1. INNER JOIN - fetches data if present in both the tables.

2. OUTER JOIN are of 3 types:

   1. LEFT OUTER JOIN - fetches data if present in the left table.
   2. RIGHT OUTER JOIN - fetches data if present in the right table.
   3. FULL OUTER JOIN - fetches data if present in either of the two tables.

3. CROSS JOIN, as the name suggests, does [n X m] that joins everything to everything.
   Similar to scenario where we simply lists the tables for joining (in the FROM clause of the SELECT statement), using commas to separate them.

---

Points to be noted:

• If you just mention JOIN then by default it is a INNER JOIN.
• An OUTER join has to be LEFT | RIGHT | FULL you can not simply say OUTER JOIN.
• You can drop OUTER keyword and just say LEFT JOIN or RIGHT JOIN or FULL JOIN.

---

For those who want to visualise these in a better way, please go to this link: A Visual Explanation of SQL Joins

Answer 4

I'm a PostgreSQL DBA, as far as I could understand the difference between outer or not outer joins difference is a topic that has considerable discussion all around the internet. Until today I never saw a difference between those two; So I went further and I try to find the difference between those. At the end I read the whole documentation about it and I found the answer for this,

So if you look on documentation (at least in PostgreSQL) you can find this phrase:

"The words INNER and OUTER are optional in all forms. INNER is the default; LEFT, RIGHT, and FULL imply an outer join."

In another words,

LEFT JOIN and LEFT OUTER JOIN ARE THE SAME

RIGHT JOIN and RIGHT OUTER JOIN ARE THE SAME

I hope it can be a contribute for those who are still trying to find the answer.

Answer 5

Nothing to say in words beside this:

Answer 6

To answer your question

In Sql Server joins syntax OUTER is optional

It is mentioned in msdn article : https://msdn.microsoft.com/en-us/library/ms177634(v=sql.130).aspx

So following list shows join equivalent syntaxes with and without OUTER

LEFT OUTER JOIN => LEFT JOIN
RIGT OUTER JOIN => RIGHT JOIN
FULL OUTER JOIN => FULL JOIN

Other equivalent syntaxes

INNER JOIN => JOIN
CROSS JOIN => ,

Strongly Recommend Dotnet Mob Artice : Joins in Sql Server