LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
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:
- INNER
- OUTER
- CROSS
INNER JOIN - fetches data if present in both the tables.
OUTER JOIN are of 3 types:
LEFT OUTER JOIN
- fetches data if present in the left table.RIGHT OUTER JOIN
- fetches data if present in the right table.FULL OUTER JOIN
- fetches data if present in either of the two tables.
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 theFROM
clause of theSELECT
statement), using commas to separate them.
Points to be noted:
- If you just mention
JOIN
then by default it is aINNER JOIN
. - An
OUTER
join has to beLEFT
|RIGHT
|FULL
you can not simply sayOUTER JOIN
. - You can drop
OUTER
keyword and just sayLEFT JOIN
orRIGHT JOIN
orFULL 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.