By KG Sosa


2009-01-02 08:30:10 8 Comments

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

12 comments

@Lasse Vågsæther Karlsen 2009-01-02 08:51:16

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

enter image description here

@Sean Reilly 2009-01-02 21:34:01

Absolutely correct. OUTER is allowed for ANSI-92 compatibility.

@nawfal 2013-05-01 14:55:03

@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 2013-05-02 07:40:10

@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 JOINs as well.

@whytheq 2013-05-13 10:19:45

@SeanReilly are you saying OUTER is required for ANSI-92 compatability? or are you saying MS prefers leaving out the OUTER but leaves it as an option in case someone is shipping in standard sql that must include OUTER ?

@Sean Reilly 2013-05-13 10:27:15

@whytheq: The first statement ("OUTER is required for ANSI-92 compatibility) is definitely true, AFAIK. A LEFT OUTER JOIN B and A LEFT JOIN B do the same thing in mssql, but only the first example is ANSI-92 compatible. The second statement ("MS prefers leaving out the OUTER but leaves it as an option in case..."), is probably also true (it is terser, and doesn't sacrifice readability that much). Of course, I don't know what MS does and doesn't prefer, but it seems sensible. Not blowing up when a third party program sends ANSI standard SQL at you is a good idea....

@whytheq 2013-05-13 10:42:15

@SeanReilly thanks Sean - it sort of begs the question "As it is the standard why make OUTER optional?"

@Sean Reilly 2013-05-13 11:07:27

@whytheq: I don't know for sure, but my guess would be that ANSI-92 compatibility was added in over time as a series of fixes. They didn't want to introduce a backwards compatibility error by making the (previously acceptable) nonstandard syntax illegal. So instead, they started accepting the term required in ANSI standard syntax, but made it optional.

@tbone 2014-02-11 14:57:26

If you just say LEFT JOIN, do you get inner or outer? To me your answer seems ambiguous.

@Lasse Vågsæther Karlsen 2014-02-11 18:52:54

"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" <-- you get an outer join.

@Outlier 2014-07-20 19:10:29

Just mention "The keyword OUTER is marked as optional" as @sactiw did. why so long answer? wastage of time.. better if answers are pointwise.. rather than being pointless. sorry for being harsh..

@Lasse Vågsæther Karlsen 2014-07-20 19:21:10

@Outlier That's your prerogative, but clearly 451 other people thought the answer was good. To be honest, if one answer says X and another answer says X and references the documentation, as well as copy the relevant pieces of the documentation into the answer, my vote goes to the second answer and that is why I write my answers the way I do. That someone claims X is not good. If you can prove X, that's better (not to slight sactiw's answer). But, its your prerogative to think whatever you want to. I question why you think it is pointless though, is the answer wrong in any way?

@Lasse Vågsæther Karlsen 2014-07-20 19:23:38

@Outlier Here's another one of my long (aka "pointless") answers that I posted today that follow the same pattern: stackoverflow.com/questions/24852609/…

@Lasse Vågsæther Karlsen 2014-07-20 19:25:27

@Outlier And to be honest, if you don't feel like reading my "long" answers, then my answer was not for you. It is for everyone else coming here wondering about the same thing and want some background on why that is, and where to find more information, etc. Clearly you are the kind of guy that would want this answer: "None", but unfortunately this is not a legal answer here on Stack Overflow, nor should it be.

@John Odom 2015-05-20 20:04:32

Lovin' the diagram for explaining the differences.

@B N 2015-06-18 06:40:39

Superb explanation, specially the diagram made it clear picture for us to remember. +1 for explaining so clearly. Thanks

@dnoeth 2015-10-12 15:56:52

According to Standard SQL OUTER is also an optional keyword: <join type> ::= INNER | <outer join type> [ OUTER ]

@smeeb 2017-02-01 15:51:47

The Venn examples here are sublime. All the joins now make complete & utter sense to me. Thank you 10,000 times over!!!

@Manachi 2017-09-26 01:14:07

It's a great thorough answer about joins in general, but I tend to agree with @Outlier, the required answer here could have been much more concise as to answering the very specific question.

@Lasse Vågsæther Karlsen 2017-11-10 09:08:27

The answer was much more concise and to the point, but people requested in comments that I described more about the other joins as well.

@philipxy 2019-04-29 01:53:12

Re abuse of Venn diagrams here see my comment on the question. PS A TL;DR is apropos since output details don't need mentioning to answer the question.

@WBT 2019-07-23 17:22:02

While OUTER might not make a difference in this particular case, a parameter being optional does NOT mean the parameter doesn't make a difference. The general statement is wrong.

@Lasse Vågsæther Karlsen 2019-07-23 17:33:05

@WBT While that may be true, that statement is not a general statement, it very clearly (in my opinion) refers to the OUTER keyword, and also states "in this case". The very next statement also mentions that while other keywords are optional, leaving them out will make a difference. I would be very interested in understanding how you made my statement to be a "general statement". English is not my native language so if there is a flaw in my statement I would like to correct it but as far as I can see, I did not make a general statement at all. Please clarify.

@WBT 2019-07-23 19:37:04

You wrote that the keyword "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." From that, a reader would likely interpret that "enclosed in square bracket" generally means "optional" (a true connection) and "optional" generally means "it makes no difference." Core issue: the "and what this means" is inaccurate. What comes before and after it are accurate but the connection drawn between them is not. Cutting those words (+replacing "is that" with a comma) and splitting the sentence would help.

@Lasse Vågsæther Karlsen 2019-07-23 21:17:06

@WBT Could you take a stab at editing the answer to be more clear. I must confess to not understand completely what you just wrote and how I should rewrite the answer.

@san 2019-06-17 02:23:09

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.

@mass 2016-06-25 04:05:20

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

@philipxy 2019-04-29 01:52:40

Re abuse of Venn diagrams here see my comment on the question.

@WorkSmarter 2015-05-22 17:09:47

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

@Delickate 2017-03-17 09:56:33

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.

@Zeek2 2018-07-20 10:44:27

So 5 joins altogether.

@andrefsp 2010-08-30 02:33:57

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.

@Mitch Wheat 2009-01-02 08:31:46

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

Nothing. LEFT JOIN and LEFT OUTER JOIN are equivalent.

@Mitch Wheat 2009-01-02 08:35:38

erm hello! who voted this down? LEFT JOIN is teh same as LEFT OUTER JOIN.

@Bill Karwin 2009-01-02 08:41:19

This is the case in Microsoft SQL Server, and any other SQL-compliant RDBMS.

@Zero3 2016-04-12 12:50:00

It would be nice if you added a reference or explanation about why the OUTER is optional.

@Mitch Wheat 2016-04-13 00:44:18

it's optional because it's unnecessary!

@Manachi 2017-09-26 01:15:41

Most concise, accurate answer here.

@KevinBui 2018-03-18 09:17:55

@MitchWheat If it's unnecessary, why it was born?

@Mitch Wheat 2018-03-18 23:12:58

@KevinBui : I don't know, I wasn't responsible for writing the language definition. But it is unnecessary, regardless. Oh, and thx for the downvote

@Bacon Brad 2018-08-09 20:36:40

Thank you. The accepted answer had way too much information that left me more confused. This is exactly what I needed to know and answers the original question straight to the point.

@sactiw 2010-12-09 18:05:03

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

@ssh 2012-12-27 19:27:00

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)

@om471987 2013-02-10 18:22:25

Also you can do cross join by simply 'select * from TableA,TableB'

@TechnicalTophat 2016-07-13 13:11:14

Sorry if I'm necrobumping, but is CROSS JOIN the same as FULL JOIN?

@sactiw 2016-07-13 15:34:50

@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

@Yugo Amaryl 2014-04-17 21:08:43

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.

@onedaywhen 2016-07-06 10:39:44

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

@Unsliced 2009-01-02 08:54:50

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

@Amy B 2009-01-02 20:29:16

So... what's a FULL OUTER JOIN then?

@Dave DuPlantis 2009-10-05 18:16:18

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.

@Harsh 2012-01-18 12:29:02

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

@frozenjim 2011-12-29 21:01:36

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.

@philipxy 2019-06-17 03:38:01

This does not answer the question.

Related Questions

Sponsored Content

29 Answered Questions

37 Answered Questions

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

3 Answered Questions

24 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

6 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

14 Answered Questions

[SOLVED] How to do a FULL OUTER JOIN in MySQL?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

10 Answered Questions

[SOLVED] Update a table using JOIN in SQL Server?

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

Sponsored Content