Sentry Answers>SQL Server>

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

Richard C.

The problemJump To Solution

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.

Click to Copy
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.

Click to Copy
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.

Click to Copy
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.

Click to Copy
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.

Click to Copy
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:

Click to Copy
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.

  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

Loved by over 4 million developers and more than 90,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.