How to join two tables without any condition

Question

For example I have two tables (temp tables). One of them contains only IDs, and the other contains real information. They have exactly the same row number.

#Table1
ID
14
15
16

#Table2
CarModel    Year
Ford        1996
Ferrari     2005
Toyota      2010

How can I join this two table in one (without any condition, just the result table has 3 columns, no matter in what order)? I need it for inserting the result for real table

Cars
ID   CarModel   Year

And this table doesn't use identity. There is an own mechanism of getting IDs for inserting. I'm not able to change it.

So as a result I need a table like one of the following (ordering is NOT important):

#Table3
ID   CarModel    Year
14   Ford        1996
15   Ferrari     2005
16   Toyota      2010

or

#Table3
ID   CarModel    Year
16   Ford        1996
14   Toyota      2010
15   Ferrari     2005

P.S. I understand how to do this with CTE and row_number(), but I think there is more easier way to do this task. And may be this code will be used in MS SQL Server 2000, so I will be glad to look at other variants of the solutions.

Vertebra

row_number() would be the way to accomplish this. However, I would have to question WHY you would want a join on these two tables if they dont actually contain related data. – Tradition

CTE and ROW_NUMBER seems the best way to me. For SQL Server 2000 you would need to simulate it with an identity column or triangular join or use cursors. – Detrude

@Tradition Thank you for your answer. I need this joined date to insert into real (not temp) table. And I need my inserted rows have IDs, which I have got from a stored procedure. But I realy don't care about what concrete id will be in each row. All I need that they all will be from my "pool" Yes it's not the best schema, but it is and I have to work with it. – Vertebra

OMG, you should really not do that! There's no guarantee SQL Server will preserve any ordering. You can't just pick a random row and say "Hey, you'll have this ID for one query!". – Standush

Answer 1

Here's a triangular join that would work in SQL Server 2000 per Martin Smith's comment:

-- Some test data:
declare @Table1 table (
    ID int primary key
)
insert into @Table1 select 14
insert into @Table1 select 15
insert into @Table1 select 16

declare @Table2 table (
    CarModel varchar(50) not null,
    [Year] smallint not null
)
insert into @Table2 select 'Ford', 1996
insert into @Table2 select 'Ferrari', 2005
insert into @Table2 select 'Toyota', 2010

-- Here's the actual query:
select Table1.ID, Table2.CarModel, Table2.[Year]
from (
        select ID, (select count(*) from @Table1 where ID < a.ID) as JoinPredicate
        from @Table1 as a
    ) as Table1
    join (
        select CarModel, [Year], (select count(*) from @Table2 where CarModel < a.CarModel or (CarModel = a.CarModel and [Year] < a.[Year])) as JoinPredicate
        from @Table2 as a
    ) as Table2 on Table1.JoinPredicate = Table2.JoinPredicate

Of course, this assumes that Table1.ID is unique and Table2.CarModel + Table2.Year is unique.

The performance considerations of triangular joins are discussed nicely here: http://www.sqlservercentral.com/articles/T-SQL/61539/

Anglicism

Why the complex casting condition? And not where CarModel < a.CarModel or (CarMoDel = a.CarModel and [Year] < a.[Year]) ? – Ong

The execution plan and statistics io are identical on my box. Anyway, it's up to the OP to make any changes to fit his code standards. – Anglicism

For a small table, it wouldn't matter. – Ong

@ypercube, I've updated to use your more elegant version of JoinPredicate. Thanks. – Anglicism