MS SQL Server - Is LEFT OUTER JOIN different from LEFT JOIN?

LEFT OUTER JOIN vs LEFT JOIN - Are they different?

If you’re new to MS SQL Server, it’s confusing to see people use LEFT JOIN for some cases, and use LEFT OUTER JOIN for some others. You might wonder if there’s a difference between LEFT JOIN and LEFT OUTER JOIN, and how to use them correctly.

The straight answer is No, there’s no difference. They produce the same result and also the same performance.

The keyword OUTER here is totally optional, which means, you can either add it to the SQL statement or not, it makes no difference. But the thing is, OUTER has to go with either LEFT, RIGHT, or FULL. You can’t use purely OUTER JOIN. In other words, it doesn’t only apply to LEFT JOIN, but also INNER JOIN and FULL JOIN.

LEFT OUTER JOIN is the same as LEFT JOIN
RIGHT OUTER JOIN is the same as RIGHT JOIN
FULL OUTER JOIN is the same as FULL JOIN

If OUTER is optinal, so why people use it?

Well, we have to go back to the reason why the word OUTER exists in the first place.

OUTER is required for ANSI-92 compatibility.

Let’s take a look at an example:

This JOIN statement:

SELECT *
FROM a LEFT JOIN b ON a.x = b.y

is equivalent to:

SELECT *
FROM a LEFT OUTER JOIN b ON a.x = b.y

But only the second statement is ANSI-92 compatible.

If it’s a standard, so why leave it as optional?

To be honest, I don’t know. Maybe it is included to remind people that it’s indeed an OUTER JOIN, not INNER JOIN?

In Oracle, it’s also optional to use OUTER in a JOIN statement. Maybe Microsoft and Oracle have an agreement on this???

There’s also no official answer for this from the SQL Server team. Maybe we just have to accept it and move on.

Need a good GUI Tool for MS SQL Server? Try TablePlus, a modern, native client for multiple databases. It’s free anyway.