What is the difference between LEFT JOIN
and LEFT OUTER JOIN
?
12 Answers
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?.
-
169Absolutely correct. OUTER is allowed for ANSI-92 compatibility. Commented Jan 2, 2009 at 21:34
-
24@LasseV.Karlsen wouldnt it be better to have
INNER JOIN
on the right and justJOIN
on the left in the list of equivalents?– nawfalCommented May 1, 2013 at 14:55 -
12@LasseV.Karlsen I just meant that the left side has the concise form and the right side has the expanded form. I thought it would make it coherent if you followed the same for
JOIN
s as well.– nawfalCommented May 2, 2013 at 7:40 -
1According to Standard SQL
OUTER
is also an optional keyword: <join type> ::= INNER | <outer join type> [ OUTER ]– dnoethCommented Oct 12, 2015 at 15:56
There is no difference between LEFT JOIN
and LEFT OUTER JOIN
, they are exactly same.
At the top level there are mainly 3 types of joins:
INNER JOIN
fetches data if present in both the tables.OUTER JOIN
s 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 times m pairings that join everything to everything. That is similar to where we simply list 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 anINNER JOIN
. - An
OUTER
join has to beLEFT
|RIGHT
|FULL
; you can not simply sayOUTER JOIN
. - You can drop the
OUTER
keyword and just sayLEFT JOIN
orRIGHT JOIN
orFULL JOIN
.
-
23Very good answer. It will be clearer if you say "LEFT OUTER JOIN - fetches all data from the left table with matching data from right, if preset." for 2.1 (and similar change for 2.2)– sshCommented Dec 27, 2012 at 19:27
-
17@RhysO no, CROSS JOIN is a Cartesian product i.e. CROSS JOIN of a table, having n rows, with a table, having m rows, will always give (n*m) rows while FULL OUTER JOIN of a table, having n rows, with a table, having m rows, will give at max (n+m) rows– sactiwCommented Jul 13, 2016 at 15:34
-
2@sactiw consider to add a special note in your valuable answer about the difference between
Cross Join
andFull Outer Join
... in some way seems similar. Commented Oct 12, 2019 at 14:51 -
1@ManuelJordan They only "seem similar" because the descriptions here are so vague & poor. But that should be clear if you take the descriptions here and try to use them to calculate example results of operator calls ... you can't, too vague & poor. Anyway everything after the first sentence is irrelevant to the question post. Also the blog at the link at the end is a mess & the author admits the blog is confused & not useful in their own comments on their blog. Venn diagrams are not useful for describing joins.– philipxyCommented Feb 3, 2023 at 10:49
What is the difference between left join and left outer join?
Nothing. LEFT JOIN
and LEFT OUTER JOIN
are equivalent.
-
14This is the case in Microsoft SQL Server, and any other SQL-compliant RDBMS. Commented Jan 2, 2009 at 8:41
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
Results
Right Join and Right Outer Join
Results
-
1Why should I not upload images of code/data/errors? Why are images of text, code and mathematical expressions discouraged?– philipxyCommented Sep 5, 2023 at 23:16
I'm a PostgreSQL DBA. The difference between outer or not outer joins is a topic that has considerable discussion all around the internet.
If you look in PostgreSQL documentation:
The words
INNER
andOUTER
are optional in all forms.INNER
is the default;LEFT
,RIGHT
, andFULL
imply an outer join. (http://www.postgresql.org/docs/8.4/static/queries-table-expressions.html)
In other words,
LEFT JOIN
and LEFT OUTER JOIN
ARE THE SAME
RIGHT JOIN
and RIGHT OUTER JOIN
ARE THE SAME
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.
-
4
In Sql Server joins syntax OUTER
is optional.
https://msdn.microsoft.com/en-us/library/ms177634(v=sql.130).aspx
Equivalent join syntaxes with and without OUTER
:
LEFT OUTER JOIN => LEFT JOIN
RIGHT OUTER JOIN => RIGHT JOIN
FULL OUTER JOIN => FULL JOIN
Other equivalent syntaxes:
INNER JOIN => JOIN
CROSS JOIN => ,
Illustration from Dotnet Mob article (strongly recommended) Joins in Sql Server:
Why are LEFT/RIGHT and LEFT OUTER/RIGHT OUTER the same? Let's explain why this vocabulary. Understand that LEFT and RIGHT joins are specific cases of the OUTER join, and therefore couldn't be anything else than OUTER LEFT/OUTER RIGHT. The OUTER join is also called FULL OUTER as opposed to LEFT and RIGHT joins that are PARTIAL results of the OUTER join. Indeed:
Table A | Table B Table A | Table B Table A | Table B Table A | Table B
1 | 5 1 | 1 1 | 1 1 | 1
2 | 1 2 | 2 2 | 2 2 | 2
3 | 6 3 | null 3 | null - | -
4 | 2 4 | null 4 | null - | -
null | 5 - | - null | 5
null | 6 - | - null | 6
OUTER JOIN (FULL) LEFT OUTER (partial) RIGHT OUTER (partial)
It is now clear why those operations have aliases, as well as it is clear only 3 cases exist: INNER, OUTER, CROSS. With two sub-cases for the OUTER. The vocabulary, the way teachers explain this, as well as some answers above, often make it looks like there are lots of different types of join. But it's actually very simple.
-
2"it is clear only 3 cases exist": interesting but flawed. Consider that an inner join is a specialised cross join (i.e. move join predicates to the where clause). Further consider that outer join isn't a join at all, rather is a union where are used nulls in place of 'missing' columns. Therefore, it could be argued that cross is the only join required. Note the current thinking in relational theory is that natural join satisfies all join requirements. Aside: can you explain if/how the vocabulary "
JOIN
impliesINNER JOIN
" fits with your reasoning for outer join vocab? Commented Jul 6, 2016 at 10:39
There are only 3 joins:
Cross Join = Cartesian Join (E.g: Table A, Table B)
Inner Join = JOIN (E.g: Table A Join / Inner Join Table B)
Outer Join
There are three types:
- Left Outer Join = Left Join
- Right Outer Join = Right Join
- Full Outer Join = Full Join
-
12
There are mainly three types of JOIN
- Inner: fetches data, that are present in both tables
- Only JOIN means INNER JOIN
Outer: are of three types
- LEFT OUTER - - fetches data present only in left table & matching condition
- RIGHT OUTER - - fetches data present only in right table & matching condition
- FULL OUTER - - fetches data present any or both table
- (LEFT or RIGHT or FULL) OUTER JOIN can be written w/o writing "OUTER"
Cross Join: joins everything to everything
Syntactic sugar, makes it more obvious to the casual reader that the join isn't an inner one.
-
2
-
6tableA FULL OUTER JOIN tableB will give you three types of records: all records in tableA with no matching record in tableB, all records in tableB with no matching record in tableA, and all records in tableA with a matching record in tableB. Commented Oct 5, 2009 at 18:16
JOINs:
INNER JOIN
=JOIN
OUTER JOIN
LEFT OUTER JOIN
=LEFT JOIN
RIGHT OUTER JOIN
=RIGHT JOIN
FULL OUTER JOIN
=FULL JOIN
CROSS JOIN
Self-JOIN
: This is not exactly a separate type of join. This is joining a table to itself using one of the above joins. You will hear this term from many in the SQL Developer community.
There are two APPLY operators:
CROSS APPLY
: Similar toINNER JOIN
(But has the added advantage of being able to compute something in the right table for each row of the left table and returns only the matching rows.)OUTER APPLY
: Similar toLEFT OUTER JOIN
(But has the added advantage of being able to compute something in the right table for each row of the left table and returns all the rows from the left table irrespective of a match on the right table.)
https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/
https://sqlhints.com/2016/10/23/outer-apply-in-sql-server/
Real life example, when to use OUTER / CROSS APPLY in SQL
The APPLY operators are very beneficial as they give better performance than doing the same computation in a subquery. They are also a replacement of many analytical functions in older versions of SQL Server. So after being comfortable with JOINS a SQL developer should learn the APPLY operators.