LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

As per the documentation: [FROM (Transact-SQL)][1]:

<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?][2].

[1]:

[To see links please register here]

[2]:

[To see links please register here]

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

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

[1]:

[To see links please register here]

In SQL, the terms "LEFT JOIN" and "LEFT OUTER JOIN" are often used interchangeably. Both of these join types return all the rows from the left table (the table specified before the JOIN keyword) and the matching rows from the right table (the table specified after the JOIN keyword). However, there is a slight difference in the way they handle unmatched rows.

A LEFT JOIN or LEFT OUTER JOIN includes all the rows from the left table, regardless of whether there is a match in the right table. If there is no match, the result will contain NULL values in the columns of the right table.

Here's an example to illustrate the difference:

Let's assume we have two tables, Customers and Orders, with a common column CustomerID:

Customers Table:

CustomerID | CustomerName
-----------|--------------
1 | John
2 | Mary
3 | David

Orders Table:

OrderID | CustomerID | OrderDate
--------|------------|-----------
101 | 1 | 2023-01-01
102 | 2 | 2023-02-01

Using a LEFT JOIN or LEFT OUTER JOIN between these tables, the result would be:


SELECT *
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result:

CustomerID | CustomerName | OrderID | CustomerID | OrderDate
-----------|--------------|---------|------------|-----------
1 | John | 101 | 1 | 2023-01-01
2 | Mary | 102 | 2 | 2023-02-01
3 | David | NULL | NULL | NULL

As you can see, the LEFT JOIN returns all rows from the left table (Customers), including the unmatched row (David) with NULL values in the columns from the right table (Orders).

With [dbForge Query Builder for SQL Server][1], you can easily select the desired join type (including LEFT JOIN or LEFT OUTER JOIN) and visually construct your queries without needing to write the SQL code manually.


[1]:

[To see links please register here]