Get Combination of Rows from Tables in Database

Getting combinations of rows from database tables is simple. First you have to understand the difference between Cartesian product and Permutation before we go any further.

I have manually printed out all the combinations in example containing three tables with respective values for the understanding.

Example
Table1     Table2     Table3
a1             b1           c1
a2             b2           c2
a3                            c3
                                c4
The results should be as follow

a1,b1,c1 a1,b1,c2 a1,b1,c3 a1,b1,c4	a1,b2,c1 a1,b2,c2 a1,b2,c3 a1,b2,c4
a2,b1,c1 a2,b1,c2 a2,b1,c3 a2,b1,c4	a2,b2,c1 a2,b2,c2 a2,b2,c3 a2,b2,c4
a3,b1,c1 a3,b1,c2 a3,b1,c3 a3,b1,c4	a3,b2,c1 a3,b2,c2 a3,b2,c3 a3,b2,c4

The above results are not permutation, because you need the combinations to always follow the unique format. So, in conclusion the Cartesian product concept is the right way to go.

Solution
We will use Cartesian Join or Cross Join for the solution of above example. Cross Join returns the Cartesian product of rows from tables in the join. Each row in the first table is matched with every row in the second table and so on.

select *
from
  table1
  cross join table2
  cross join table3

Same thing as implicit cross join:

select *
from
  table1, table2, table3

/Adnan