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
Feedback
Do you have any feedback about this page? We'd love to hear it!