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

Question

Lets say I have two subqueries:

SELECT Id AS Id0 FROM Table0

=>

Id0
---
1
2
3

and

SELECT Id AS Id1 FROM Table1

=>


Id1
---
4
5
6

How do I combine these to get the query result:

Id0 Id1
-------
1   4
1   5
1   6
2   4
2   5
2   6
3   4
3   5
3   6

Answer 1

Cartesian join, a join with no 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 query if you want 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

Answer 2

Try this :

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

Grégoire

Answer 3

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