All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | Development versions: 3.12 | Unsupported versions: 3.6 | 3.5 | 3.4 | 3.3 | 3.2 | 2.6

The JOIN operators that can be used in SQL SELECT statements are the most powerful and best supported means of creating new table expressions in SQL. Informally, the following can be said:

A(colA1, ..., colAn) "join" B(colB1, ..., colBm) "produces" C(colA1, ..., colAn, colB1, ..., colBm)

SQL and relational algebra distinguish between at least the following JOIN types (upper-case: SQL, lower-case: relational algebra):

  • CROSS JOIN or cartesian product: The basic JOIN in SQL, producing a relational cross product, combining every record of table A with every record of table B. Note that cartesian products can also be produced by listing comma-separated table expressions in the FROM clause of a SELECT statement
  • NATURAL JOIN: The basic JOIN in relational algebra, yet a rarely used JOIN in databases with everyday degree of normalisation. This JOIN type unconditionally equi-joins two tables by all columns with the same name (requiring foreign keys and primary keys to share the same name). Note that the JOIN columns will only figure once in the resulting table expression.
  • INNER JOIN or equi-join: This JOIN operation performs a cartesian product (CROSS JOIN) with a filtering predicate being applied to the resulting table expression. Most often, a equal comparison predicate comparing foreign keys and primary keys will be applied as a filter, but any other predicate will work, too.
  • OUTER JOIN: This JOIN operation performs a cartesian product (CROSS JOIN) with a filtering predicate being applied to the resulting table expression. Most often, a equal comparison predicate comparing foreign keys and primary keys will be applied as a filter, but any other predicate will work, too. Unlike the INNER JOIN, an OUTER JOIN will add "empty records" to the left (table A) or right (table B) or both tables, in case the conditional expression fails to produce a .
  • semi-join: In SQL, this JOIN operation can only be expressed implicitly using IN predicates or EXISTS predicates. The table expression resulting from a semi-join will only contain the left-hand side table A
  • anti-join: In SQL, this JOIN operation can only be expressed implicitly using NOT IN predicates or NOT EXISTS predicates. The table expression resulting from a semi-join will only contain the left-hand side table A
  • division: This JOIN operation is hard to express at all, in SQL. See the manual's chapter about relational division for details on how jOOQ emulates this operation.

jOOQ supports all of these JOIN types (including semi-join and anti-join) directly on any table expression:

// jOOQ's relational division convenience syntax
DivideByOnStep divideBy(Table<?> table)

// INNER JOIN
TableOnStep join(TableLike<?>)
TableOnStep innerJoin(TableLike<?>)

// OUTER JOIN (supporting Oracle's partitioned OUTER JOIN)
TablePartitionByStep leftJoin(TableLike<?>)
TablePartitionByStep leftOuterJoin(TableLike<?>)

TablePartitionByStep rightJoin(TableLike<?>)
TablePartitionByStep rightOuterJoin(TableLike<?>)

// FULL OUTER JOIN
TableOnStep fullOuterJoin(TableLike<?>)

// SEMI JOIN
TableOnStep<R> leftSemiJoin(TableLike<?>);

// ANTI JOIN
TableOnStep<R> leftAntiJoin(TableLike<?>);

// CROSS JOIN
Table<Record> crossJoin(TableLike<?>)

// NATURAL JOIN
Table<Record> naturalJoin(TableLike<?>)
Table<Record> naturalLeftOuterJoin(TableLike<?>)
Table<Record> naturalRightOuterJoin(TableLike<?>)

Most of the above JOIN types are overloaded also to accommodate plain SQL use-cases for convenience:

// Standard overload accepting a formal jOOQ table reference
TableOnStep join(TableLike<?>)

// Overloaded versions taking SQL template strings with bind variables, or other forms of
// "plain SQL" QueryParts:
TableOnStep join(String)
TableOnStep join(String, Object...)
TableOnStep join(String, QueryPart...)
TableOnStep join(SQL)
TableOnStep join(Name)

Note that most of jOOQ's JOIN operations give way to a similar DSL API hierarchy as previously seen in the manual's section about the JOIN clause

The jOOQ Logo