Search code examples
sqlmergecombinationsansi-sql

How to merge 2 columns with all possible combinations in SQL?

The question sounds confusing, but just look:

This way we can get the first column (col1):

select distinct maker
from product

And the second column (col2):

select distinct type,maker
from product

So now I need to get all possibly combinations from col1 and col2. Any suggestion?

Shortly, this:

A f1

B f2

Should become this:

A f1

A f2

B f1

B f2

P.S. This query won't return that I need.

select distinct A.maker, B.type
from product as A
Solution

  • Use cross join to get all combinations:

    select m.maker, t.type
from (select distinct maker from product) m cross join
     (select distinct type from product) t;

    This is ANSI SQL syntax and should be supported in any database.