LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

Question

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

Answer 1

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.

Answer 2

Read more at Visual Representation of SQL Joins

Answer 3

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

Answer 4

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