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.