22

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

alt text

According to me that first row it should be Mohit but output is different. Please tell me.

starball
  • 20,030
  • 7
  • 43
  • 238
Mohit Kumar
  • 1,885
  • 5
  • 21
  • 24

6 Answers6

17

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
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • +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". – onedaywhen Nov 01 '10 at 15:27
  • Thanks for the reply.I know about order by But I want to know that how Inner join works internally? – Mohit Kumar Nov 03 '10 at 08:13
  • Build the plan for your query (just press `Ctrl-L` in the query window), it will show you the algorithm. – Quassnoi Nov 03 '10 at 08:57
8

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.

Syed Ali hassan
  • 596
  • 4
  • 16
dotariel
  • 1,584
  • 12
  • 23
  • Thanks for the reply.I know about order by But I want to know that how Inner join works internally? – Mohit Kumar Nov 03 '10 at 08:12
  • 2
    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. – dotariel Nov 03 '10 at 12:49
  • 4
    Anyone knows if I can force (specially for SQLite) the join to be "ordered"? – Gustavo Meira Jul 02 '14 at 11:18
8

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;
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
3

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

By default there is no ordering.

JNK
  • 63,321
  • 15
  • 122
  • 138
  • but when selecting the records from the table that time Default order by is "ASC" – Vijjendra Nov 01 '10 at 14:41
  • Thanks for the reply.I know about order by But I want to know that how Inner join works internally? – Mohit Kumar Nov 03 '10 at 08:14
  • @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. – JNK Nov 03 '10 at 12:02
1

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
Tyug
  • 443
  • 2
  • 7
  • 21
0

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

.

Muzamir
  • 88
  • 1
  • 8
  • 2
    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.](//stackoverflow.com/help/deleted-answers) – 4b0 Aug 07 '18 at 06:28