LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

Question

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

Answer 1

There are only 3 joins:

• A) Cross Join = Cartesian (E.g: Table A, Table B)
• B) Inner Join = JOIN (E.g: Table A Join/Inner Join Table B)

• C) Outer join:

     There are three type of outer join
     1)  Left Outer Join     = Left Join
     2)  Right Outer Join    = Right Join
     3)  Full Outer Join     = Full Join    
  

Hope it'd help.

Answer 2

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), and what this means in this case is that whether you specify it or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will of course 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?.

Answer 3

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

Answer 4

As its already mentioned here, there is NO difference. The key world OUTER could be removed from SQL keyword dictionary without we losing expressional power on the queries we are able to write.

Also, we do not need both RIGHT and LEFT keywords as only one of them is sufficient. This is because SELECT * FROM A RIGHT JOIN B on A.ID = B.ID is exactly same as SELECT * FROM B LEFT JOIN A on A.ID = B.ID. I have found all these extra terms confusing students and novice people.

Answer 5

I find it easier to think of Joins in the following order:

• CROSS JOIN - a Cartesian product of both tables. ALL joins begin here
• INNER JOIN - a CROSS JOIN with a filter added.
• OUTER JOIN - an INNER JOIN with missing elements (from either LEFT or RIGHT table) added afterward.

Until I figured out this (relatively) simple model, JOINS were always a bit more of a black art. Now they make perfect sense.

Hope this helps more than it confuses.

Answer 6

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

Nothing. LEFT JOIN and LEFT OUTER JOIN are equivalent.