MySQL cartesian product between two SELECT statements

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 |
+--------+--------+

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.

Tags:

Mysql

Select

Join