SQL 内部连接中的排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4069784/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Order by in Inner Join
提问by Mohit Kumar
I am putting inner join in my query.I have got the result but didn't know that how the data is coming in output.Can anyone tell me that how the Inner join matching the data.Below I am showing a image.There are two table(One or Two Table).
我在我的查询中加入了内连接。我得到了结果，但不知道数据是如何进入输出的。谁能告诉我内连接如何匹配数据。下面我展示了一个图像。有两张桌子（一张或两张桌子）。
According to me that first row it should be Mohit but output is different.Please tell me.
据我说，第一行应该是 Mohit 但输出不同。请告诉我。
Thanks in advance.
提前致谢。
采纳答案by dotariel
You have to sort it if you want the data to come back a certain way. When you say you are expecting "Mohit" to be the first row, I am assuming you say that because "Mohit" is the first row in the [One]table. However, when SQL Server joins tables, it doesn't necessarily join in the order you think.
如果您希望数据以某种方式返回，则必须对其进行排序。当您说您期望“ Mohit”是第一行时，我假设您这么说是因为“ Mohit”是[One]表中的第一行。但是，当 SQL Server 联接表时，它不一定按照您认为的顺序联接。
If you want the first row from [One]to be returned, then try sorting by [One].[ID]. Alternatively, you can order byany other column.
如果您希望[One]返回第一行，请尝试按 排序[One].[ID]。或者，您可以使用order by任何其他列。
回答by Quassnoi
In SQL, the order of the output is not defined unless you specify it in the ORDER BYclause.
在 中SQL，除非您在ORDER BY子句中指定，否则不会定义输出顺序。
Try this:
尝试这个：
SELECT *
FROM one
JOIN two
ON one.one_name = two.one_name
ORDER BY
one.id
回答by onedaywhen
Avoid SELECT *in your main query.
避免SELECT *在您的主要查询中。
Avoid duplicate columns: the JOINcondition ensures One.One_Nameand two.One_Namewill be equal therefore you don't need to return both in the SELECTclause.
避免重复列：JOIN条件确保One.One_Name和two.One_Name将相等，因此您不需要在SELECT子句中返回两者。
Avoid duplicate column names: rename One.IDand Two.IDusing 'aliases'.
避免重复的列名：重命名One.ID和Two.ID使用“别名”。
Add an ORDER BYclause using the column names ('alises' where applicable) from the SELECTclause.
使用ORDER BY子句中的列名称（适用时为“别名”）添加SELECT子句。
Suggested re-write:
建议改写：
SELECT T1.ID AS One_ID, T1.One_Name,
T2.ID AS Two_ID, T2.Two_name
FROM One AS T1
INNER JOIN two AS T2
ON T1.One_Name = T2.One_Name
ORDER
BY One_ID;
回答by JNK
Add an ORDER BY ONE.ID ASCat the end of your first query.
ORDER BY ONE.ID ASC在第一个查询的末尾添加一个。
By default there is no ordering.
默认情况下没有排序。
回答by Tyug
SQL doesn't return any ordering by default because it's faster this way. It doesn't have to go through your data first and then decide what to do.
默认情况下，SQL 不返回任何排序，因为这种方式速度更快。它不必先查看您的数据，然后再决定要做什么。
You need to add an order by clause, and probably order by which ever ID you expect. (There's a duplicate of names, thus I'd assume you want One.ID)
您需要添加一个 order by 子句，并且可能按您期望的 ID 进行排序。（有重复的名字，因此我假设你想要 One.ID）
select * From one
inner join two
ON one.one_name = two.one_name
ORDER BY one.ID
回答by Muzamir
I found this to be an issue when joining but you might find this blog useful in understanding how Joins work in the back. How Joins Work..
我发现这是加入时的一个问题，但您可能会发现此博客有助于理解加入在后面的工作方式。 联接如何工作..
[Edited] @Shree Thank you for pointing that out. On the paragraph of Merge Join. It mentions on how joins work...
[编辑] @Shree 感谢您指出这一点。在 Merge Join 的段落上。它提到了连接的工作方式......
Like hash join, merge join consists of two steps. First, both tables of the join are sorted on the join attribute. This can be done with just two passes through each table via an external merge sort. Finally, the result tuples are generated as the next ordered element is pulled from each table and the join attributes are compared
与散列连接一样，合并连接由两个步骤组成。首先，连接的两个表都按连接属性排序。这可以通过外部合并排序通过每个表两次来完成。最后，当从每个表中提取下一个有序元素并比较连接属性时，生成结果元组
.
.
