为什么我们不能通过交换表来使用左外连接，而不是右外连接? [英] Why can't we use left outer join by swapping the tables, instead of Right outer Join?
问题描述
几天前，我在一次采访中遇到了以下问题.什么是右外连接?"我回答说，右外连接连接两个表并返回两个表中匹配的记录以及表 A 中不匹配的行".面试官只是笑了笑，又问:为什么我们使用右连接，而不是我们可以交换表格并使用左连接?".然后我的回答是，是的，我们可以使用".但是，他认为那为什么会有两个连接的概念?(左和右)".我真的对这个问题感到困惑.请帮助我并纠正我对连接的理解.
few days back, I have faced the question in an interview as following. "what is Right outer join?" I answered, " Right outer join joins two tables and returns the matched records from both the tables as well as non matching rows from Table A". the interviewer simply laughed and asked again, "Why we use Right join rather we can swap the tables and use left join?". Then my answer was," yes, we can use". But, he argued that "then why the concept of two joins? (left and right)" . I really got confused with this question. Please help me on this and correct my understanding on joins.
推荐答案
LEFT [OUTER] JOIN 和 RIGHT [OUTER] JOIN 完全可以互换strong> 如果您也重新排列表格的顺序.
LEFT [OUTER] JOIN and RIGHT [OUTER] JOIN are completely interchangeable if you rearrange the order of the tables as well.
换句话说，以下四个连接子句将产生相同的结果行:
In other words, the following four join clauses will produce the same resulting rows:
A LEFT JOIN B ON A.X = B.Y
B RIGHT JOIN A ON A.X = B.Y
A LEFT JOIN B ON B.Y = A.X -- switched A.X = B.Y around
B RIGHT JOIN A ON B.Y = A.X
结果完全没有区别.
这对您作为程序员来说很方便.
This is a convenience to you as a programmer.
另见这个问题:
这意味着这个问题的答案:
This means that the answer to this question:
为什么我们使用右连接而不是交换表并使用左连接?
Why we use Right join rather we can swap the tables and use left join?
这是:
因为您想使用右连接而不是左连接.以这种方式编写 SQL 可能更自然，或者您只是喜欢 RIGHT 这个词而不是 LEFT 这个词.
Because you wanted to use Right join instead of Left join. It may be more natural to write the SQL that way, or you just like the word RIGHT more than the word LEFT.
注意:如果您在同一个查询中混合 LEFT 和 RIGHT 连接，您可能会得到一些奇怪的结果，但您没有提到这些.
Note: If you mix LEFT and RIGHT joins in the same query, you might get some odd results, but you mention none of that.
注意，这是语法.如果数据库引擎使用顺序来选择索引等，执行性能可能会有所不同.然而，数据方面的最终结果应该是完全相同的.不过，我不知道任何此类性能技巧，因此可能没有，但很可能有.
Note, this is syntax. There might be a difference in execution performance if the database engine uses the order to pick indexes and similar. The end result, data-wise, should be the exact same, however. I have no knowledge of any such performance tricks though, so there may be none, but there very well may be.
如果执行计划因表排序而不同，则结果顺序也可能有所不同.IE.数据库引擎将选择一个表作为主表，并为另一个表执行散列连接或类似操作，这可能会以不同的顺序返回行.但是，除非您专门对行进行排序，否则包含相同行的两个结果集是等效的，即使它们的行顺序不同.我发现这比产生性能差异的可能性要小，因为其中一个表总是可能比另一个表贡献更多的行，因此选择哪个作为主表应该是相同的.
There may also be a difference in the resulting order, if the execution plans differ because of table ordering. Ie. the database engine will pick one table as a master and do a hash join or similar for the other, which may return the rows in a different order. However, unless you specifically order the rows, two result sets containing the same rows are equivalent, even if they don't have the rows in the same order. I find this less likely than the chance of a performance difference since one of the tables will always potentially contribute more rows to the result than the other, so which to pick as a master should be the same either way.
这篇关于为什么我们不能通过交换表来使用左外连接，而不是右外连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
