完全外连接与完全连接

通过查询和示例来更好地理解连接。我注意到，在 SQLServer2008中，以下两个查询给出了相同的结果:

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

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

它们是执行完全相同的操作以产生相同的结果，还是在更复杂的示例中会遇到不同的结果？这只是可以互换的术语吗？

206526 次浏览
小开
最佳答案

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.

小开

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.

小开

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.


提交答案