LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

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

As per the documentation: FROM (Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets). In this specific case, whether you specify OUTER or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will make a difference.

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here's a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.

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

What is the difference between left join and left outer join?

Nothing. LEFT JOIN and LEFT OUTER JOIN are equivalent.

Left Join and Left Outer Join are one and the same. The former is the shorthand for the latter. The same can be said about the Right Join and Right Outer Join relationship. The demonstration will illustrate the equality. Working examples of each query have been provided via SQL Fiddle. This tool will allow for hands on manipulation of the query.

Given

Left Join and Left Outer Join

Results

Right Join and Right Outer Join

Results

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.