How to join two tables together with same number of rows by their order

Question

I am using SQL2000 and I would like to join two table together based on their positions

For example consider the following 2 tables:

table1
-------
name
-------
'cat'
'dog'
'mouse'

table2
------
cost
------
23
13
25

I would now like to blindly join the two table together as follows based on their order not on a matching columns (I can also guarantee both tables have the same number of rows):

-------|-----
name   |cost
-------|------
'cat'  |23
'dog'  |13
'mouse'|25

Is this possible in a T-SQL select??

Inweave

why.....just why would you build a database to be used in this way? The whole point of databases is to not do this sort of thing. – Arthrospore

Answer 1

This is NOT possible, since there's absolutely no guarantee regarding the order in which the rows will be selected.

There are a number of ways to achieve what you want (see other answers) provided you're lucky regarding the sorting order, but none will work if you aren't, and you shouldn't rely on such queries.

Being forced to do this kind of queries strongly smells of a bad database design.

Herbalist

Answer 2

If your tables aren't two large, you could create two temp tables in memory and select your content into them in a specific order, and then join them on the row Number.

e.g.

CREATE TABLE #Temp_One (
    [RowNum] [int] IDENTITY (1, 1) NOT NULL ,
    [Description] [nvarchar] (50) NOT NULL
)

CREATE TABLE #Temp_Two (
    [RowNum] [int] IDENTITY (1, 1) NOT NULL ,
    [Description] [nvarchar] (50) NOT NULL
)

INSERT INTO #Temp_One
SELECT Your_Column FROM Your_Table_One ORDER BY Whatever

INSERT INTO #Temp_Two
SELECT Your_Column FROM Your_Table_Two ORDER BY Whatever

SELECT * 
FROM #Temp_One a 
    LEFT OUTER JOIN #Temp_Two b 
         On a.RowNum = b.RowNum

Womanly

What makes you think those temp tables are in-memory? – Granulation

Turn of phrase... I know they're in TempDB... my point is this is a useful way of doing what he wants for tables with a couple of thousand rows, but not for tables with several million rows... – Womanly

If you want in memory tables you will need to declare them as table var (with the @ symbol) the thing that restricts the size of the temp tables here is the size of the tempdb database. – Rerun

Answer 3

in 2000 you will either have to run 2 forward only cursors and insert into a temp table. or insert the values into a temp table with an extra identity column and join the 2 temp tables on the identity field

Rerun

@Mike, it depends on what you are trying to achieve, if you need to run an operation on each pair then a cursor set could potentially be faster than pushing all the data into a set of temp tables (which was my second suggestion) – Rerun

Answer 4

Do you have anything that guarantees ordering of each table?

As far ax I know, SQL server does not make any promise on the ordering of a resultset unless the outer query has an order by clause. In your case you need Each table to be ordered in a deterministic manner for this to work.

Other than that, in SQL 2000, as answered before me, a temp table and two cursors seem like a good answer.

Update: Someone mentioned inserting both tables into temp tables, and that it would yield better performance. I am no SQL expert so I defer to those who know on that front, and since I had an up-vote I thought you should investigate those performance considerations. But in any case, if you do not have any other information in your tables than what you showed us I'm not sure you can pull it off, ordering-wise.

Hafler

Answer 5

Absolutely. Use the following query but make sure that (order by) clause uses the same columns the order of rows will change which you dont want.

select
(
row_number() over(order by name) rno, * from Table1
) A  
(
row_number() over(order by name) rno, * from Table2
) B
JOIN A.rno=B.rno

order by clause can be modified according to user linkings

The above query produces unique row_numbers for each row, which an be joined with row_numbers of the other table

Glottis

Answer 6

You could alter both tables to have an auto_increment column, then join on that.

As others have told you, SQL has no intrinsic ordering; a table of rows is a set. Any ordering you get is arbitrary, unless you add an order by clause.

So yeah, there are ways you can do this, but all of them depend on the accidental ordering being what you hope it is. So do this this once, and don't do it again unless you can come up with a way (auto_increments, natural keys, something) to ensure ordering.

Granulation

Answer 7

Consider using a rank (rownum in Oracle) to dynamically apply ordered unique numbers to each table. Simply join on the rank column and you should have what you need. See this Microsoft article on numbering rows.

Grounds

Answer 8

would be best to use row_number(), but that is only for 2005 and 2008, this should work for 2000...

Try this:

create table table1 (name varchar(30))
insert into table1 (name) values ('cat')
insert into table1 (name) values ('dog')
insert into table1 (name) values ('mouse')

create table table2 (cost int)
insert into table2 (cost) values (23)
insert into table2 (cost) values (13)
insert into table2 (cost) values (25)

Select IDENTITY(int,1,1) AS RowNumber
, Name
INTO #Temp1
from table1


Select IDENTITY(int,1,1) AS RowNumber
, Cost
INTO #Temp2
from table2

select * from #Temp1
select * from #Temp2

SELECT
    t1.Name, t2.Cost
    FROM #Temp1                 t1
        LEFT OUTER JOIN #Temp2  t2 ON t1.RowNumber=t2.RowNumber
    ORDER BY t1.RowNumber

Intelligible

Why a left outer join, if you know both tables have the same # of rows? – Ahimsa

Answer 9

Xynth - built in row numbering is not available until SQL2K5 unfortunately, and the example given by microsoft actually uses triangular joins - a horrific hidden performance hit if the tables get large. My preferred approach would be an insert into a pair of temp tables using the identity function and then join on these, which is basically the same answer already given. I think the two-cursors approach sounds much heavier than it needs to be for this task.

Pelpel

I agree that performance would be terrible, but if you're planning on using this DB going forward I think that performance probably won't be your biggest problem. I was hoping you were looking for a solution to transform your database into a more maintable incarnation. – Grounds

Ah ok. That makes sense then. (sudden nightmare of our systems actually being like this with no keys etc...agh.) – Pelpel

Answer 10

This is a fully working version example:

DECLARE
    @name VARCHAR(MAX) = 'cat,dog,mouse',
    @cost VARCHAR(MAX) = '23,13,25'

SELECT 
    name.value as name, 
    cost.value as cost
FROM
(
    SELECT 
    ROW_NUMBER() OVER(ORDER BY (select 0)) AS RowNum,
    value
    FROM STRING_SPLIT(@name, ',')
) name
JOIN
(
    SELECT 
    ROW_NUMBER() OVER(ORDER BY (select 0)) AS RowNum,
    value
    FROM STRING_SPLIT(@cost, ',')
) cost ON cost.RowNum = name.RowNum

Akanke