MySQL cartesian product between two SELECT statements

Question

I want to perform a cartesian product between two SELECT statements as

SELECT 1, 2     INNER JOIN     SELECT 3, 4 ;

I expect the result to be every combination of (1,2) with (3,4), like:

1  3
2  3
1  4
2  4

Answer 1

If you specify your tables with out any JOIN ON clause or equalities/conditionins in the WHERE clause you'll get the catesian product you're looking for.

SELECT table1.field1, table2.field2
FROM table1, table2

will give you what you're asking for. Showing it more explicitly...

SELECT * FROM table1;
+--------+
| field1 |
+--------+
|      1 |
|      2 |
+--------+

SELECT * FROM table2;
+--------+
| field2 |
+--------+
|      3 |
|      4 |
+--------+

SELECT table1.field1, table2.field2 FROM table1, table2;
+--------+--------+
| field1 | field2 |
+--------+--------+
|      1 |      3 |
|      2 |      3 |
|      1 |      4 |
|      2 |      4 |
+--------+--------+

Answer 2

You can use the CROSS JOIN clause

SELECT MyTable1.Col1, MyTable2.Col2
FROM MyTable1
CROSS JOIN MyTable2

where MyTable1 has two rows containing 1 and 2; and MyTable2 has two rows containing 3 and 4.

Answer 3

select v1, v2
from
  (select 1 as v1 union
   select 2) t1,
  (select 3 as v2 union
   select 4) t2

or even simpler:

select *
from
  (select 1 union
   select 2) t1,
  (select 3 union
   select 4) t2