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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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        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

User · Answer

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

User · Answer

Add an ORDER BY ONE ID ASC at the end of your first query   By default there is no ordering

[sql] Order by in Inner Join

Examples related to sql

Examples related to sql-server

Examples related to inner-join