1933

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

Quiver
  • 1,270
  • 5
  • 31
  • 50
KG Sosa
  • 20,309
  • 6
  • 25
  • 27
  • Those who reached here for `PostgreSQL`, the answer is the same: *None*! The [word OUTER is optional](https://www.postgresql.org/docs/current/queries-table-expressions.html) – Ricardo Jan 28 '22 at 01:17

12 Answers12

2602

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

philipxy
  • 14,416
  • 5
  • 32
  • 77
Lasse V. Karlsen
  • 366,661
  • 96
  • 610
  • 798
  • 154
    Absolutely correct. OUTER is allowed for ANSI-92 compatibility. – Sean Reilly Jan 02 '09 at 21:34
  • 19
    @LasseV.Karlsen wouldnt it be better to have `INNER JOIN` on the right and just `JOIN` on the left in the list of equivalents? – nawfal May 01 '13 at 14:55
  • 11
    @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. – nawfal May 02 '13 at 07:40
  • 1
    According to Standard SQL `OUTER` is also an optional keyword: ** ::= INNER | [ OUTER ]** – dnoeth Oct 12 '15 at 15:56
797

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

SriniV
  • 10,691
  • 14
  • 59
  • 88
sactiw
  • 21,037
  • 4
  • 38
  • 28
  • 20
    Very 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) – ssh Dec 27 '12 at 19:27
  • 1
    Also you can do cross join by simply 'select * from TableA,TableB' – om471987 Feb 10 '13 at 18:22
  • 3
    Sorry if I'm necrobumping, but is `CROSS JOIN` the same as `FULL JOIN`? – TechnicalTophat Jul 13 '16 at 13:11
  • 11
    @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 – sactiw Jul 13 '16 at 15:34
  • 1
    @sactiw consider to add a special note in your valuable answer about the difference between `Cross Join` and `Full Outer Join` ... in some way seems similar. – Manuel Jordan Oct 12 '19 at 14:51
426

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

Nothing. LEFT JOIN and LEFT OUTER JOIN are equivalent.

Mitch Wheat
  • 288,400
  • 42
  • 452
  • 532
79

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

enter image description here

Left Join and Left Outer Join

enter image description here

Results

enter image description here

Right Join and Right Outer Join

enter image description here

Results

enter image description here

xdhmoore
  • 7,670
  • 9
  • 43
  • 78
WorkSmarter
  • 3,558
  • 2
  • 27
  • 33
75

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.

shA.t
  • 15,880
  • 5
  • 49
  • 104
andrefsp
  • 767
  • 5
  • 2
53

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.

frozenjim
  • 706
  • 5
  • 11
41

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.

Yugo Amaryl
  • 1,229
  • 2
  • 12
  • 19
  • 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` implies `INNER JOIN`" fits with your reasoning for outer join vocab? – onedaywhen Jul 06 '16 at 10:39
40

To answer your question

In Sql Server joins syntax OUTER is optional

It is mentioned in msdn article : https://msdn.microsoft.com/en-us/library/ms177634(v=sql.130).aspx

So following list shows join equivalent 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 => ,

Strongly Recommend Dotnet Mob Artice : Joins in Sql Server enter image description here

MD. Khairul Basar
  • 4,628
  • 12
  • 41
  • 54
mass
  • 532
  • 4
  • 7
26

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
TylerH
  • 20,816
  • 57
  • 73
  • 92
Delickate
  • 968
  • 1
  • 10
  • 16
22

There are mainly three types of JOIN

  1. Inner: fetches data, that are present in both tables
    • Only JOIN means INNER JOIN

  2. 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"

  3. Cross Join: joins everything to everything

sjngm
  • 11,933
  • 13
  • 78
  • 107
Harsh
  • 237
  • 2
  • 3
19

Syntactic sugar, makes it more obvious to the casual reader that the join isn't an inner one.

Uncle Iroh
  • 5,450
  • 5
  • 46
  • 57
Unsliced
  • 10,165
  • 8
  • 49
  • 81
  • 2
    So... what's a FULL OUTER JOIN then? – Amy B Jan 02 '09 at 20:29
  • 6
    tableA 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. – Dave DuPlantis Oct 05 '09 at 18:16
14

Just in the context of this question, I want to post the 2 'APPLY' operators as well:

JOINS:

  1. INNER JOIN = JOIN

  2. OUTER JOIN

    • LEFT OUTER JOIN = LEFT JOIN

    • RIGHT OUTER JOIN = RIGHT JOIN

    • FULL OUTER JOIN = FULL JOIN

  3. CROSS JOIN

SELF-JOIN: This is not exactly a separate type of join. This is basically joining a table to itself using one of the above joins. But I felt it is worth mentioning in the context JOIN discussions as you will hear this term from many in the SQL Developer community.

APPLY:

  1. CROSS APPLY -- Similar to INNER JOIN (But has added advantage of being able to compute something in the Right table for each row of the Left table and would return only the matching rows)
  2. OUTER APPLY -- Similar to LEFT OUTER JOIN (But has added advantage of being able to compute something in the Right table for each row of the Left table and would return 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

I find APPLY operator very beneficial as they give better performance than having to do the same computation in a subquery. They are also replacement of many Analytical functions in older versions of SQL Server. That is why I believe that after being comfortable with JOINS, one SQL developer should try to learn the APPLY operators next.

san
  • 1,345
  • 7
  • 12