How to generate permutations using SQL?

Database
Enthusiast

How to generate permutations using SQL?

How can I generate all permutation from the variables selected from different tables using SQL

For Example

In Table1 with Var1 has values:
Var1
1
2
3

In Table2 with Var2 has values:
Var2
4
5

In Table3 with Var3 has values:
Var3
7
8
9

I want to create a table with all the permutations, i.e.,
Var1 Var2 Var3
---------------------------------
1 4 7
1 4 8
1 4 9
2 4 7
2 4 8
2 4 9
3 4 7
3 4 8
3 4 9
1 5 7
1 5 8
1 5 9
2 5 7
2 5 8
2 5 9
3 5 7
3 5 8
3 5 9

Regards,

Prakash
1 REPLY
Senior Apprentice

Re: How to generate permutations using SQL?

Simply CROSS JOIN those tables:

select * from table1 CROSS JOIN table2 CROSS JOIN table3

or using old comma delimited syntax:
select * from table1, table2, table3

Dieter