LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

left-join, outer-join, sql-server, t-sql

Solution

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?.

Problem

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

Original source

Related problems