FULL OUTER JOIN vs. FULL JOIN

Question

Just playing around with queries and examples to get a better understanding of joins. I'm noticing that in SQL Server 2008, the following two queries give the same results:

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

---

SELECT * FROM TableA
FULL JOIN TableB
ON TableA.name = TableB.name

Are these performing exactly the same action to produce the same results, or would I run into different results in a more complicated example? Is this just interchangeable terminology?

Answer 1

Actually they are the same. LEFT OUTER JOIN is same as LEFT JOIN and RIGHT OUTER JOIN is same as RIGHT JOIN. It is more informative way to compare from INNER Join.

See this Wikipedia article for details.

Answer 2

Microsoft® SQL Server™ 2000 uses these SQL-92 keywords for outer joins specified in a FROM clause:

• LEFT OUTER JOIN or LEFT JOIN

• RIGHT OUTER JOIN or RIGHT JOIN

• FULL OUTER JOIN or FULL JOIN

From MSDN

The full outer join or full join returns all rows from both tables, matching up the rows wherever a match can be made and placing NULLs in the places where no matching row exists.

Answer 3

It's true that some databases recognize the OUTER keyword. Some do not. Where it is recognized, it is usually an optional keyword. Almost always, FULL JOIN and FULL OUTER JOIN do exactly the same thing. (I can't think of an example where they do not. Can anyone else think of one?)

This may leave you wondering, "Why would it even be a keyword if it has no meaning?" The answer boils down to programming style.

In the old days, programmers strived to make their code as compact as possible. Every character meant longer processing time. We used 1, 2, and 3 letter variables. We used 2 digit years. We eliminated all unnecessary white space. Some people still program that way. It's not about processing time anymore. It's more about fast coding.

Modern programmers are learning to use more descriptive variables and put more remarks and documentation into their code. Using extra words like OUTER make sure that other people who read the code will have an easier time understanding it. There will be less ambiguity. This style is much more readable and kinder to the people in the future who will have to maintain that code.