CODEWITHSUNDEEP
mysqlselectjoin
bobobobo
bobobobo

Reputation: 67254

MySQL cartesian product between two SELECT statements

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

Upvotes: 12

Views: 35526

Answers (5)

cxwangyi
cxwangyi

Reputation: 701

mysql> select * from
    ->   (select 1 x union select 2 union select 3 union select 4) t
    ->   join
    ->   (select 1 y union select 2 union select 3) tt;
+---+---+
| x | y |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |
+---+---+
12 rows in set (0.00 sec)

Removing the word join gives exactly the same result.

select * from
  (select 1 x union select 2 union select 3 union select 4) t,
  (select 1 y union select 2 union select 3) tt;

Upvotes: 1

Ashish Kumar
Ashish Kumar

Reputation: 1005

With the using this your format is not as you are saying A(1,2) and B(3,4) then the cross join will perform it like this:

SELECT Table1.Col1, Table2.Col2
FROM Table1
CROSS JOIN Table2

(A*B)= (1,3),(1,4),(2,3),(2,4)

Upvotes: 0

Riedsio
Riedsio

Reputation: 9926

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

Upvotes: 17

Athari
Athari

Reputation: 34295

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

Upvotes: 1

bobs
bobs

Reputation: 22194

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.

Upvotes: 15

Related Questions