Categories
left-join outer-join sql-server tsql

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

1959

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

0

    2630

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

    4

    • 158

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

      Jan 2, 2009 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 1, 2013 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 JOINs as well.

      – nawfal

      May 2, 2013 at 7:40

    • 1

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

      – dnoeth

      Oct 12, 2015 at 15:56

    808

    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

    5

    • 21

      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, 2012 at 19:27


    • 3

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

      – om471987

      Feb 10, 2013 at 18:22

    • 4

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

      Jul 13, 2016 at 13:11


    • 13

      @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, 2016 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.

      Oct 12, 2019 at 14:51

    434

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

    Nothing. LEFT JOIN and LEFT OUTER JOIN are equivalent.

    1

    • 11

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

      Jan 2, 2009 at 8:41