COMPLETE INTERNAL CONNECTION versus COMPLETE CONNECTION

Question

Just play around with queries and examples to better understand joins. I notice 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 

Do they perform the same action to get the same results, or will I encounter different results in a more complex example? Is this just interchangeable terminology?

Answer 1

In fact, they are the same. LEFT OUTER JOIN same as the LEFT JOIN , and the RIGHT OUTER JOIN same as the RIGHT JOIN . This is a more informative way to compare with INNER Join .

See the Wikipedia article for more details.

Answer 2

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

• LEFT INTERCONNECT INTERACTOR OR LEFT CONNECTION

• ROTATING INTERACTION OR CORRECT ENTRANCE

• FULL INTERNAL ENTRY OR FULL JOINING

From MSDN

full outer join or full join returns all rows from both tables, matching rows wherever a match can be made, and placing NULL in places where the corresponding row does not exist.

Answer 3

It is true that some databases recognize the OUTER keyword. Some do not. Where it is recognized, this is usually an optional keyword. Almost always, FULL JOIN AND FULL EXTERNAL JOIN do the same. (I cannot come up with an example where they do not. Can anyone else think about this?)

This may leave you at a loss: "Why would it be a keyword if it doesn't make sense?" The answer comes down to the programming style.

In the old days, programmers sought to make their code as compact as possible. Each symbol meant longer processing time. We used 1, 2, and 3 letter variables. We used two-digit years. We have eliminated all the excess empty space. Some people still program this way. This is not a processing time. This is more about fast coding.

Modern programmers learn to use more descriptive variables and put more comments and documentation in their code. When using additional words such as OUTER, make sure that other people who read the code find it easier to understand. There will be less ambiguity. This style is much more readable and kinder to people in the future who will have to support this code.

Answer 4

Delete on TableA.name = TableB.name you will see the Cartesian product.