Order by in Inner Join

Question

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.

Thanks in advance.

Solution 1

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.

If you want the first row from [One] to be returned, then try sorting by [One].[ID]. Alternatively, you can order by any other column.

Solution 2

In SQL, the order of the output is not defined unless you specify it in the ORDER BY clause.

Try this:

SELECT  *
FROM    one
JOIN    two
ON      one.one_name = two.one_name
ORDER BY
        one.id

Solution 3

Avoid SELECT * in your main query.

Avoid duplicate columns: the JOIN condition ensures One.One_Name and two.One_Name will be equal therefore you don't need to return both in the SELECT clause.

Avoid duplicate column names: rename One.ID and Two.ID using 'aliases'.

Add an ORDER BY clause using the column names ('alises' where applicable) from the SELECT clause.

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;

Solution 4

Add an ORDER BY ONE.ID ASC at the end of your first query.

By default there is no ordering.

Solution 5

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.

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)

select * From one
inner join two
ON one.one_name = two.one_name
ORDER BY one.ID

View more solutions

Comments

[Vijjendra]

but when selecting the records from the table that time Default order by is "ASC"

[onedaywhen]

+1: with an ORDER BY clause, order is unlikely to be random and is likely to be predictable (given enough information) but the point is "output is not defined".

[Mohit Kumar]

Thanks for the reply.I know about order by But I want to know that how Inner join works internally?

[Mohit Kumar]

Thanks for the reply.I know about order by But I want to know that how Inner join works internally?

[Mohit Kumar]

Thanks for the reply.I know about order by But I want to know that how Inner join works internally?

[Quassnoi]

Build the plan for your query (just press Ctrl-L in the query window), it will show you the algorithm.

[JNK]

@Mohit - There is no natural order once you do the JOIN. The results you got were correct, but unordered. Your single table SELECT is in the order it is probably because of a clustered index.

[dotariel]

When you do an INNER JOIN, SQL Server determines the best way to find the matching rows (nested loops, etc). This results in a random ordering of the results.

[Gustavo Meira]

Anyone knows if I can force (specially for SQLite) the join to be "ordered"?

[4b0]

Muzamir, while this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Answers that are little more than a link may be deleted.