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.
几天前,我在接受采访时面临的问题如下。 “什么是右外连接?”我回答说,“右外连接连接两个表,并从表中返回匹配的记录以及表A中的非匹配行”。面试官只是笑了,又问道:“为什么我们使用Right join而不是我们可以交换表并使用左连接?”。然后我的回答是,“是的,我们可以使用”。但是,他认为“那么为什么两个概念加入了?(左和右)”。我真的对这个问题感到困惑。请帮助我,并纠正我对连接的理解。
2 个解决方案
#1
4
LEFT [OUTER] JOIN and RIGHT [OUTER] JOIN are completely interchangeable if you rearrange the order of the tables as well.
如果重新排列表格的顺序,LEFT [OUTER] JOIN和RIGHT [OUTER] JOIN完全可以互换。
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
There's absolutely no difference in the results.
结果完全没有区别。
This is a convenience to you as a programmer.
作为程序员,这对您来说很方便。
See also this question:
另见这个问题:
- Difference between left join and right join in SQL Server
SQL Server中左连接和右连接之间的区别
This means that the answer to this question:
这意味着这个问题的答案:
Why we use Right join rather we can swap the tables and use left join?
为什么我们使用右连接而不是我们可以交换表并使用左连接?
is this:
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.
因为您想使用右连接而不是左连接。以这种方式编写SQL可能更自然,或者你只喜欢单词RIGHT而不是单词LEFT。
Note: If you mix LEFT and RIGHT joins in the same query, you might get some odd results, but you mention none of that.
注意:如果在同一查询中混合使用LEFT和RIGHT联接,可能会得到一些奇怪的结果,但是你没有提到这一点。
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.
注意,这是语法。如果数据库引擎使用顺序来选择索引等,则执行性能可能会有所不同。然而,数据方面的最终结果应该完全相同。我不知道任何这样的表演技巧,所以可能没有,但很有可能。
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.
如果执行计划因表排序而不同,则生成的顺序也可能存在差异。 IE浏览器。数据库引擎将选择一个表作为主表,并为另一个表执行散列连接或类似操作,这可能以不同的顺序返回行。但是,除非您专门对行进行排序,否则包含相同行的两个结果集是等效的,即使它们没有相同顺序的行也是如此。我发现这比性能差异的可能性小,因为其中一个表总是可能为结果贡献比另一个更多的行,所以选择作为主数据的方式应该是相同的。
#2
0
This is an interesting article/discussion on the topic. From the answers I think the following sums it up rather well (by Jeremiah Peschka): Convenience, optimization and it being the ANSI standard.
这是一篇关于该主题的有趣文章/讨论。从答案我认为以下总结相当好(由Jeremiah Peschka):便利性,优化和它是ANSI标准。
Convenience and optimization. Just because we can write our query as a LEFT OUTER JOIN, doesn’t mean that you should. SQL Server provides a RIGHT OUTER JOIN showplan operator (http://msdn.microsoft.com/en-us/library/ms190390.aspx). There are times when it’s going to be most efficient to use a right outer join. Leaving that option in the language 1) gives you the same functionality in the language that you have in the optimizer and 2) supports the ANSI SQL specification. There’s always a chance, in a sufficiently complex plan on a sufficiently overloaded SQL Server, that SQL Server may time out query compilation. In theory, if you specify RIGHT OUTER JOIN instead of a LEFT OUTER JOIN, your SQL could provide SQL Server with the hints it needs to create a better plan. If you ever see this situation, though, you should probably blog about it :)
方便和优化。仅仅因为我们可以将查询编写为LEFT OUTER JOIN,并不意味着你应该这样做。 SQL Server提供了一个RIGHT OUTER JOIN showplan运算符(http://msdn.microsoft.com/en-us/library/ms190390.aspx)。有时候使用正确的外连接会最有效。在语言1)中保留该选项可以使用优化器中的语言提供相同的功能,并且2)支持ANSI SQL规范。在一个足够复杂的SQL Server服务器上,总有一个机会,SQL Server可能会超时查询编译。理论上,如果指定RIGHT OUTER JOIN而不是LEFT OUTER JOIN,则SQL可以为SQL Server提供创建更好计划所需的提示。但是,如果你看到这种情况,你应该写博客:)
No programming task requires a join, but you can also write all of your queries using syntax like SELECT * FROM a, b, c, d WHERE (a.id = b.a_id OR b.a_id IS NULL) and still have perfectly valid, well-formed, and ANSI compliant SQL.
没有编程任务需要连接,但您也可以使用SELECT * FROM a,b,c,d WHERE(a.id = b.a_id或b.a_id IS NULL)等语法编写所有查询,并且仍然完全有效,格式良好,符合ANSI标准的SQL。
#1
4
LEFT [OUTER] JOIN and RIGHT [OUTER] JOIN are completely interchangeable if you rearrange the order of the tables as well.
如果重新排列表格的顺序,LEFT [OUTER] JOIN和RIGHT [OUTER] JOIN完全可以互换。
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
There's absolutely no difference in the results.
结果完全没有区别。
This is a convenience to you as a programmer.
作为程序员,这对您来说很方便。
See also this question:
另见这个问题:
- Difference between left join and right join in SQL Server
SQL Server中左连接和右连接之间的区别
This means that the answer to this question:
这意味着这个问题的答案:
Why we use Right join rather we can swap the tables and use left join?
为什么我们使用右连接而不是我们可以交换表并使用左连接?
is this:
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.
因为您想使用右连接而不是左连接。以这种方式编写SQL可能更自然,或者你只喜欢单词RIGHT而不是单词LEFT。
Note: If you mix LEFT and RIGHT joins in the same query, you might get some odd results, but you mention none of that.
注意:如果在同一查询中混合使用LEFT和RIGHT联接,可能会得到一些奇怪的结果,但是你没有提到这一点。
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.
注意,这是语法。如果数据库引擎使用顺序来选择索引等,则执行性能可能会有所不同。然而,数据方面的最终结果应该完全相同。我不知道任何这样的表演技巧,所以可能没有,但很有可能。
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.
如果执行计划因表排序而不同,则生成的顺序也可能存在差异。 IE浏览器。数据库引擎将选择一个表作为主表,并为另一个表执行散列连接或类似操作,这可能以不同的顺序返回行。但是,除非您专门对行进行排序,否则包含相同行的两个结果集是等效的,即使它们没有相同顺序的行也是如此。我发现这比性能差异的可能性小,因为其中一个表总是可能为结果贡献比另一个更多的行,所以选择作为主数据的方式应该是相同的。
#2
0
This is an interesting article/discussion on the topic. From the answers I think the following sums it up rather well (by Jeremiah Peschka): Convenience, optimization and it being the ANSI standard.
这是一篇关于该主题的有趣文章/讨论。从答案我认为以下总结相当好(由Jeremiah Peschka):便利性,优化和它是ANSI标准。
Convenience and optimization. Just because we can write our query as a LEFT OUTER JOIN, doesn’t mean that you should. SQL Server provides a RIGHT OUTER JOIN showplan operator (http://msdn.microsoft.com/en-us/library/ms190390.aspx). There are times when it’s going to be most efficient to use a right outer join. Leaving that option in the language 1) gives you the same functionality in the language that you have in the optimizer and 2) supports the ANSI SQL specification. There’s always a chance, in a sufficiently complex plan on a sufficiently overloaded SQL Server, that SQL Server may time out query compilation. In theory, if you specify RIGHT OUTER JOIN instead of a LEFT OUTER JOIN, your SQL could provide SQL Server with the hints it needs to create a better plan. If you ever see this situation, though, you should probably blog about it :)
方便和优化。仅仅因为我们可以将查询编写为LEFT OUTER JOIN,并不意味着你应该这样做。 SQL Server提供了一个RIGHT OUTER JOIN showplan运算符(http://msdn.microsoft.com/en-us/library/ms190390.aspx)。有时候使用正确的外连接会最有效。在语言1)中保留该选项可以使用优化器中的语言提供相同的功能,并且2)支持ANSI SQL规范。在一个足够复杂的SQL Server服务器上,总有一个机会,SQL Server可能会超时查询编译。理论上,如果指定RIGHT OUTER JOIN而不是LEFT OUTER JOIN,则SQL可以为SQL Server提供创建更好计划所需的提示。但是,如果你看到这种情况,你应该写博客:)
No programming task requires a join, but you can also write all of your queries using syntax like SELECT * FROM a, b, c, d WHERE (a.id = b.a_id OR b.a_id IS NULL) and still have perfectly valid, well-formed, and ANSI compliant SQL.
没有编程任务需要连接,但您也可以使用SELECT * FROM a,b,c,d WHERE(a.id = b.a_id或b.a_id IS NULL)等语法编写所有查询,并且仍然完全有效,格式良好,符合ANSI标准的SQL。