The jOOQ User Manual : SQL building : SQL Statements (DML) : The SELECT statement : JOIN operator

previous : next

JOIN operator

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

jOOQ supports many different types of standard and non-standard SQL JOIN operations:

• [ INNER ] JOIN
• LEFT [ OUTER ] JOIN
• RIGHT [ OUTER ] JOIN
• FULL OUTER JOIN
• LEFT SEMI JOIN
• LEFT ANTI JOIN
• CROSS JOIN
• NATURAL JOIN
• NATURAL LEFT [ OUTER ] JOIN
• NATURAL RIGHT [ OUTER ] JOIN

Besides, jOOQ also supports

• CROSS APPLY (T-SQL and Oracle 12c specific)
• OUTER APPLY (T-SQL and Oracle 12c specific)
• LATERAL derived tables (PostgreSQL and Oracle 12c)
• partitioned outer join

All of these JOIN methods can be called on org.jooq.Table types, or directly after the FROM clause for convenience. The following example joins AUTHOR and BOOK

DSLContext create = DSL.using(connection, dialect);

// Call "join" directly on the AUTHOR table
Result<?> result = create.select()
                         .from(AUTHOR.join(BOOK)
                                     .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
                         .fetch();

// Call "join" on the type returned by "from"
Result<?> result = create.select()
                         .from(AUTHOR)
                         .join(BOOK)
                         .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
                         .fetch();

The two syntaxes will produce the same SQL statement. However, calling "join" on org.jooq.Table objects allows for more powerful, nested JOIN expressions (if you can handle the parentheses):

SELECT *
FROM AUTHOR
LEFT OUTER JOIN (
  BOOK JOIN BOOK_TO_BOOK_STORE
       ON BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
)
ON BOOK.AUTHOR_ID = AUTHOR.ID
 

// Nest joins and provide JOIN conditions only at the end
create.select()
      .from(AUTHOR
      .leftOuterJoin(BOOK
        .join(BOOK_TO_BOOK_STORE)
        .on(BOOK_TO_BOOK_STORE.BOOK_ID.eq(BOOK.ID)))
      .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
      .fetch();

• See the section about conditional expressions to learn more about the many ways to create org.jooq.Condition objects in jOOQ.
• See the section about table expressions to learn about the various ways of referencing org.jooq.Table objects in jOOQ

JOIN ON KEY, convenience provided by jOOQ

Surprisingly, the SQL standard does not allow to formally JOIN on well-known foreign key relationship information. Naturally, when you join BOOK to AUTHOR, you will want to do that based on the BOOK.AUTHOR_ID foreign key to AUTHOR.ID primary key relation. Not being able to do this in SQL leads to a lot of repetitive code, re-writing the same JOIN predicate again and again - especially, when your foreign keys contain more than one column. With jOOQ, when you use code generation, you can use foreign key constraint information in JOIN expressions as such:

SELECT *
FROM AUTHOR
JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
 

create.select()
      .from(AUTHOR)
      .join(BOOK).onKey()
      .fetch();

In case of ambiguity, you can also supply field references for your foreign keys, or the generated foreign key reference to the onKey() method.

Note that formal support for the Sybase JOIN ON KEY syntax is on the roadmap.

The JOIN USING syntax

Most often, you will provide jOOQ with JOIN conditions in the JOIN .. ON clause. SQL supports a different means of specifying how two tables are to be joined. This is the JOIN .. USING clause. Instead of a condition, you supply a set of fields whose names are common to both tables to the left and right of a JOIN operation. This can be useful when your database schema has a high degree of relational normalisation. An example:

-- Assuming that both tables contain AUTHOR_ID columns
SELECT *
FROM AUTHOR
JOIN BOOK USING (AUTHOR_ID)
 

// join(...).using(...)
create.select()
      .from(AUTHOR)
      .join(BOOK).using(AUTHOR.AUTHOR_ID)
      .fetch();

In schemas with high degrees of normalisation, you may also choose to use NATURAL JOIN, which takes no JOIN arguments as it joins using all fields that are common to the table expressions to the left and to the right of the JOIN operator. An example:

-- Assuming that both tables contain AUTHOR_ID columns
SELECT *
FROM AUTHOR
NATURAL JOIN BOOK
 

// naturalJoin(...)
create.select()
      .from(AUTHOR)
      .naturalJoin(BOOK)
      .fetch();

Oracle's partitioned OUTER JOIN

Oracle SQL ships with a special syntax available for OUTER JOIN clauses. According to the Oracle documentation about partitioned outer joins this can be used to fill gaps for simplified analytical calculations. jOOQ only supports putting the PARTITION BY clause to the right of the OUTER JOIN clause. The following example will create at least one record per AUTHOR and per existing value in BOOK.PUBLISHED_IN, regardless if an AUTHOR has actually published a book in that year.

SELECT *
FROM AUTHOR
LEFT OUTER JOIN BOOK
PARTITION BY (PUBLISHED_IN)
ON BOOK.AUTHOR_ID = AUTHOR.ID
 

create.select()
      .from(AUTHOR)
      .leftOuterJoin(BOOK)
      .partitionBy(BOOK.PUBLISHED_IN)
      .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .fetch();

SEMI JOIN and ANTI JOIN

Very few databases (e.g. Apache Impala) ship with a built-in syntax for { LEFT | RIGHT } SEMI JOIN and { LEFT | RIGHT } ANTI JOIN, which are much more concise versions of the SQL standard IN / EXISTS and NOT IN / NOT EXISTS predicates. The idea is that the JOIN syntax is expressed where it belongs, in the FROM clause, not in the WHERE clause.

Since jOOQ 3.7, these types of JOIN are also supported and they're emulated using EXISTS and NOT EXISTS respectively.

Here's how SEMI JOIN translates to EXISTS.

SELECT FIRST_NAME, LAST_NAME
FROM AUTHOR
WHERE EXISTS (
  SELECT 1 FROM BOOK WHERE AUTHOR.ID = BOOK.AUTHOR_ID
)

create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .from(AUTHOR)
      .leftSemiJoin(BOOK)
      .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .fetch();

And here's how ANTI JOIN translates to NOT EXISTS

SELECT FIRST_NAME, LAST_NAME
FROM AUTHOR
WHERE NOT EXISTS (
  SELECT 1 FROM BOOK WHERE AUTHOR.ID = BOOK.AUTHOR_ID
)

create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .from(AUTHOR)
      .leftAntiJoin(BOOK)
      .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .fetch();

Notice that according to Relational algebra's understanding of left semi / anti join, the right hand side of the left semi / anti join operator is not projected, i.e. it cannot be accessed from WHERE or SELECT or any other clause than ON.

T-SQL's CROSS APPLY and OUTER APPLY

T-SQL has long known what the SQL standard calls lateral derived tables, lateral joins using the APPLY keyword. To every row resulting from the table expression on the left, we apply the table expression on the right. This is extremely useful for table-valued functions, which are also supported by jOOQ. Some examples:

DSL.using(configuration)
   .select()
   .from(AUTHOR,
         lateral(select(count().as("c"))
                .from(BOOK)
                .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
   )
   .fetch("c", int.class);

The above example shows standard usage of the LATERAL keyword to connect a derived table to the previous table in the FROM clause. A similar statement can be written in T-SQL:

DSL.using(configuration)
   .from(AUTHOR)
   .crossApply(
       select(count().as("c"))
      .from(BOOK)
      .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
   )
   .fetch("c", int.class)

While not all forms of LATERAL JOIN have an equivalent APPLY syntax, the inverse is true, and jOOQ can thus emulate OUTER APPLY and CROSS APPLY using LATERAL JOIN.

LATERAL JOIN or CROSS APPLY are particularly useful together with table valued functions, which are also supported by jOOQ.

The jOOQ User Manual : SQL building : SQL Statements (DML) : The SELECT statement : JOIN operator

previous : next