cartesian product on two tables

Question

I'm trying to create a result set of table names and column names. In table A I have the list of table names and in Table B I have the list of column names. Each table should have the full list of columns. So:

• Table A

table_name
Table1
Table2

• Table B

column_name
Column1
Column2
Column3

The result I want is:

table_name	column_name
Table1	Column1
Table1	Column2
Table1	Column3
Table2	Column1
Table2	Column2
Table2	Column3

So I have select

select
        table_name
        column_name
from
        A
full outer join
        B
on
        A.TABLE_NAME = B.COLUMN_NAME

Which gives me:

table_name	column_name
Table1
Table2
	Column1
	Column2
	Column3

Close, but no cigar! How can I get my answer please?

Answer 1

try this if your tables are not too big:

select
    A.table_name
    , B.column_name
from A
cross join B

Answer 2

Use this:

select A.table_name, B.column_name from A, B;