Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9

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.

Dialect support

This example using jOOQ:

select(BOOK.ID, AUTHOR.ID).from(BOOK.crossJoin(AUTHOR))

Translates to the following dialect specific expressions:

-- ASE
SELECT BOOK.ID, AUTHOR.ID
FROM BOOK
  JOIN AUTHOR
    ON 1 = 1

-- AURORA_MYSQL, AURORA_POSTGRES, BIGQUERY, COCKROACHDB, DB2, DERBY, EXASOL, FIREBIRD, H2, HANA, HSQLDB, INFORMIX, 
-- MARIADB, MEMSQL, MYSQL, ORACLE, POSTGRES, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, 
-- VERTICA, YUGABYTEDB
SELECT BOOK.ID, AUTHOR.ID
FROM BOOK
  CROSS JOIN AUTHOR

-- ACCESS, DUCKDB
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website

References to this page

Feedback

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

The jOOQ Logo