How to preserve order of temp table rows when inner joined with another table?
- by Triynko
Does an SQL Server "join" preserve any kind of row order consistently (i.e. that of the left table or that of the right table)?
Psuedocode:
create table #p (personid bigint);
foreach (id in personid_list)
insert into #p (personid) values (id)
select id from users inner join #p on users.personid = #p.id
Suppose I have a list of IDs that correspond to person entries. Each of those IDs may correspond to zero or more user accounts (since each person can have multiple accounts).
To quickly select columns from the users table, I populate a temp table with person ids, then inner join it with the users table.
I'm looking for an efficient way to ensure that the order of the results in the join matches the order of the ids as they were inserted into the temp table, so that the user list that's returned is in the same order as the person list as it was entered.
I've considered the following alternatives:
using "#p inner join users", in case the left table's order is preserved
using "#p left join users where id is not null", in case a left join preserves order and the inner join doesn't
using "create table (rownum int, personid bigint)", inserting an incrementing row number as the temp table is populated, so the results can be ordered by rownum in the join
using an SQL Server equivalent of the "order by order of [tablename]" clause available in DB2
I'm currently using option 3, and it works... but I hate the idea of using an order by clause for something that's already ordered. I just don't know if the temp table preserves the order in which the rows were inserted or how the join operates and what order the results come out in.
