New versions: Dev (3.16) | Latest (3.15) | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3

CROSS JOIN

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

A CROSS JOIN creates a cartesian product or cross product between the two tables it joins. It does not allow for any join predicates to be specified.

It is an occasionally useful operator in reporting, when every element of one set need to be combined with every element of another set. For example, when you want to produce a report combining employees and weekdays, and then do something with the resulting table:

SELECT EMPLOYEE.NAME, WEEKDAY.NAME
FROM EMPLOYEE
CROSS JOIN WEEKDAY
 
create.select(EMPLOYEE.NAME, WEEKDAY.NAME)
      .from(EMPLOYEE)
      .crossJoin(WEEKDAY)
      .fetch();

Some example output might be:

+---------------+--------------+
| EMPLOYEE.NAME | WEEKDAY.NAME |
+---------------+--------------+
| Jon           | Monday       |
| Jon           | Tuesday      |
| Jon           | Wednesday    |
| Jon           | Thursday     |
| Jon           | Friday       |
| Jon           | Saturday     |
| Jon           | Sunday       |
| Jane          | Monday       |
| Jane          | Tuesday      |
| Jane          | Wednesday    |
| Jane          | Thursday     |
| Jane          | Friday       |
| Jane          | Saturday     |
| Jane          | Sunday       |
| ...           | ...          |
+---------------+--------------+

Table lists

Note that a CROSS JOIN is functionally (but not syntactically) equivalent to a table list that you can provide in the FROM clause:

SELECT EMPLOYEE.NAME, WEEKDAY.NAME
FROM EMPLOYEE, WEEKDAY
 
create.select(EMPLOYEE.NAME, WEEKDAY.NAME)
      .from(EMPLOYEE, WEEKDAY)
      .fetch();

It is usually recommended to prefer the CROSS JOIN syntax in order to clearly communicate intent.

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo