how to maintain order while doing join in sql

Question

table 1 and table 2 have their primary key (key1,key2) to be the same. table 3 is a user defined table with one column field5 which is common to table2. I need to select the rows from table 1 and table 2 where field5 in table2 is in table3. I need to obtain the rows in the same order as table3.

table 1

key1 key2 field1 field2

table 2

key1 key2 field3 field4 field5

user defined table

field5

the query i am thinking is

select a.key1, a.key2, a.field1, a.field2, b.field3, b.field4
from table1 as a INNER JOIN table2 as b ON a.key1 = b.key1 AND a.key2 = b.key2
where b.field5 in (select field5 from table3)

this will not give me the rows in the same order as in table3.

I would need something like this to maintain the order?

select a.key1, a.key2, a.field1, a.field2, d.field3, d.field4 from
table1 as a INNER JOIN
 (select b.key1, b.key2, b.field3, b.field4
  from table2 as b INNER JOIN table3 as c
  ON b.field5 = c.field5) as d
WHERE a.key1 = d.key1 AND a.key2 = d.key2

Answer 1

Order is only offered in the final result when you use an order by clause.

If you want "in the same order as table3" you must write a proper order by clause to recreate the order that you have in table3.

The join is free to scramble rows if it makes things go faster.

Answer 2

If you would like to order output data, as it is ordered (inserted) in table3, I'd suggest, adding a column to table3 rowid int identity(1,1). This creates an autoincrement field, which will increase its value for each new row inserted.

Then you can select rows as they were inserted into that table as selecting order by table3.rowid

Reference from MSDN - IDENTITY (Property)

Answer 3

You cannot depend on the physical ordering of rows in a table. To ensure a given order, add a sort field to your table3, and change your query to

select 
  a.key1, 
  a.key2, 
  a.field1, 
  a.field2, 
  b.field3, 
  b.field4 
from table1 as a 
INNER JOIN table2 as b ON a.key1 = b.key1 AND a.key2 = b.key2 
INNER JOIN table3 as c on b.field5 = c.field5
ORDER BY c.sort_field