LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

Richard C.

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.

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.

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.

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.

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.

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:

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.