LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

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

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.

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

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

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.


[1]:

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

**Nothing**. `LEFT JOIN` and `LEFT OUTER JOIN` are equivalent.

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."](

[To see links please register here]

)

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.

`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][1]

**[Left Join][2] and [Left Outer Join][3]**

![enter image description here][4]

**Results**

![enter image description here][5]


----------


**[Right Join][6] and [Right Outer Join][7]**

![enter image description here][8]

**Results**

![enter image description here][9]


[1]:

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[5]:

[6]:

[To see links please register here]

[7]:

[To see links please register here]

[8]:

[9]:

To answer your question

> In Sql Server joins syntax **OUTER** is optional

It is mentioned in msdn article :

[To see links please register here]

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][1]
[![enter image description here][2]][1]


[1]:

[To see links please register here]

[2]:

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)

[To see links please register here]

[To see links please register here]

[To see links please register here]

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.