# LEFT JOIN vs LEFT OUTER JOIN in SQL Server Explained

> LEFT JOIN and LEFT OUTER JOIN are identical in SQL Server and all databases, with examples comparing cross, inner, left, right, and full outer join behavior

**URL:** https://sentry.io/answers/left-join-vs-left-outer-join-in-sql-server/

---

## The problem

What is the difference between a `LEFT JOIN` and a `LEFT OUTER JOIN` in SQL Server? What about in standard SQL or other database servers like MySQL and PostgreSQL? And how exactly do these joins relate to others, like `INNER JOIN`, `FULL JOIN`, and `CROSS JOIN`?

## The solution

The short answer is that there is no difference between a `LEFT JOIN` and a `LEFT OUTER JOIN`. They return identical results. (This is true for all database servers and the ANSI and ISO SQL standard, not just SQL Server.)

### List of identical joins with different names

There is redundancy in the syntax of joins. This means that the pairs of queries in each line below are identical:

- `LEFT JOIN` and `LEFT OUTER JOIN`
- `RIGHT JOIN` and `RIGHT OUTER JOIN`
- `FULL JOIN` and `FULL OUTER JOIN`
- `INNER JOIN` and `JOIN`.

To understand joins fully, let's look at a simple example.

Here are two tables, Person and Item, with two rows each. One person, Amir, owns one item, a chair. Sofia owns nothing and the bag is owned by no one.

```sql
CREATE TABLE Person
(
    Id INT PRIMARY KEY,
    Name VARCHAR(255),
);

CREATE TABLE Item
(
    Id INT PRIMARY KEY,
    Name VARCHAR(255),
    PersonId INT,
    FOREIGN KEY (PersonId) REFERENCES Person(Id)
);

INSERT INTO Person(Id, Name)
VALUES
    (1, 'Amir'),
    (2, 'Sofia');

INSERT INTO Item(Id, Name, PersonId)
VALUES
    (1, 'Chair', 1),
    (2, 'Bag', null);
```

### Cross join

The simplest join is the `CROSS JOIN`, which returns all results in both tables.

```sql
SELECT *
FROM
  Person P
  CROSS JOIN Item I;

-- Id	Name	Id	Name	PersonId
-- 1	Amir	1	Chair	1
-- 2	Sofia	1	Chair	1
-- 1	Amir	2	Bag	(null)
-- 2	Sofia	2	Bag	(null)
```

You are almost never going to use this in your job and don't need to remember it, but it is one of the fundamentals of relational database theory.

### Inner join

Then, an `INNER JOIN`, or just a `JOIN`, is a cross join with a filter, that returns only rows from both tables that match the filter.

```sql
SELECT *
FROM
  Person P
  JOIN Item I
    ON P.Id = I.PersonId;

-- Id	Name	Id	Name	PersonId
-- 1	Amir	1	Chair	1
```

This is the most common and useful join you will use.

### Left and right outer joins

Next, a `LEFT JOIN` OR `LEFT OUTER JOIN`, returns the same result as in inner join, but includes all rows in the left table, even if they don't match the filter.

Below is a left join on the Person table, then on the Item table.

```sql
SELECT *
FROM
  Person P
  LEFT JOIN Item I
    ON P.Id = I.PersonId;

-- Id	Name	Id	    Name	PersonId
-- 1	Amir	1	    Chair	1
-- 2	Sofia	(null)	(null)	(null)

SELECT *
FROM
  Item I
  LEFT JOIN Person P
    ON P.Id = I.PersonId;

-- Id	Name	PersonId	Id	Name
-- 1	Chair	1	        1	Amir
-- 2	Bag	    (null)	    (null)	(null)
```

A `RIGHT` join works the same way, except you must swap the order of the tables in your query. (The second query above could have been a right join if the table order hadn't been swapped.)

An outer join is useful if you want to find disconnected rows in tables — rows that don't have foreign keys to another table.

### Full join

A `FULL OUTER JOIN` or `FULL JOIN` combines the left and right joins. You can see below that a full join on the table is the combined results of both queries above.

```sql
SELECT *
FROM
  Person P
  FULL JOIN Item I
    ON P.Id = I.PersonId;

-- Id	    Name	Id	    Name	PersonId
-- 1	    Amir	1	    Chair	1
-- 2	    Sofia	(null)	(null)	(null)
-- (null)	(null)	2	    Bag	    (null)
```

Note that a full join is different to a cross join. If your two tables in the join have M and N number of rows, then a cross join will have M×N rows. If either table is empty then a cross join will return 0 rows.

A full join will always return some rows, unless both tables are empty. It will return at minimum M or N rows, whichever is larger (if every row matches the filter). The maximum number of rows a full join will return is M+N rows (if no row matches the filter).

### Summary

To work in SQL you need to remember only the following three joins:

```sql
SELECT *
FROM A JOIN B
ON A.Id = B.ForeignId;

SELECT *
FROM A LEFT JOIN B
ON A.Id = B.ForeignId;

SELECT *
FROM A FULL JOIN B
ON A.Id = B.ForeignId;
```

Other than cross join, every other join you see containing the word `INNER` or `OUTER` is a variation of the syntax above.

---

*Source: [sentry.io/answers/left-join-vs-left-outer-join-in-sql-server/](https://sentry.io/answers/left-join-vs-left-outer-join-in-sql-server/)*