How can I get the permutations of elements from two subqueries in T-SQL?

Question

Let's say I have two sub queries:

SELECT Id AS Id0 FROM Table0

=>

Id0
---
1
2
3

and

SELECT Id AS Id1 FROM Table1

=>


Id1
---
4
5
6

How to combine them to get the query result:

+1

Niels Bosma May 13 '09 at 8:31 am

3 answers

Cartesian union, union without a join condition

select id0.id as id0, id1.id as id1 
from id0, id1

Alternatively, you can use the CROSS JOIN syntax if you prefer

select id0.id as id0, id1.id as id1 
from id0 cross join id1

you can order your request if you want to get a specific order, from your example it looks like you want

select id0.id as id0, id1.id as id1
from id0 cross join id1 order by id0.id, id1.id

+1

codeulike May 13 '09 at 8:33

SELECT Table0.Id0, Table1.Id1 FROM Table0 Full Join table 1 1 = 1

+1

TheKido 22 Feb 10 at 9:06

podosta · Accepted Answer · 2009-05-13T08:35:40+0000

Try the following:

SELECT A.Id0, B.Id1
FROM (SELECT Id AS Id0 FROM Table0) A, 
     (SELECT Id AS Id1 FROM Table1) B

Gregoire