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

ON clause

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

All of INNER JOIN, OUTER JOIN, SEMI JOIN, ANTI JOIN require a join predicate.

One way to supply this join predicate is the ON clause, which offers most flexibility. The following example shows how to "equi join" the author and books tables based on their FOREIGN KEY relationship:

SELECT *
FROM AUTHOR
JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
 
create.select()
      .from(AUTHOR)
      .join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .fetch();

But in most dialects, any type of join predicate is possible in ON to specify what rows should be produced by the join operation. Note that while for INNER JOIN, the predicates in the ON clause and the predicates in the WHERE clause have the same effect, this isn't true for all the other join types, including OUTER JOIN, SEMI JOIN, ANTI JOIN. For example, the following query will list all authors and their books, but only if the book was published before the year 1950:

SELECT *
FROM AUTHOR
LEFT JOIN BOOK
  ON BOOK.AUTHOR_ID = AUTHOR.ID
  AND BOOK.PUBLISHED_IN < 1950
 
create.select()
      .from(AUTHOR)
      .leftJoin(BOOK)
        .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
        .and(BOOK.PUBLISHED_IN.lt(1950))
      .fetch();

The result might look like this:

+------------+-----------+--------------+
| FIRST_NAME | LAST_NAME | TITLE        |
+------------+-----------+--------------+
| George     | Orwell    | 1984         |
| George     | Orwell    | Animal Farm  | <-- This author's books were all published before 1950
| Paulo      | Coelho    |              | <-- This author's books were published after 1950
+------------+-----------+--------------+

We still get all the authors, but only the books that fulfil the ON predicate. This is very different from putting that additional predicate in the WHERE clause:

SELECT *
FROM AUTHOR
LEFT JOIN BOOK
  ON BOOK.AUTHOR_ID = AUTHOR.ID
WHERE BOOK.PUBLISHED_IN < 1950
 
create.select()
      .from(AUTHOR)
      .leftJoin(BOOK)
        .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
      .where(BOOK.PUBLISHED_IN.lt(1950))
      .fetch();

The result might now look like this:

+------------+-----------+--------------+
| FIRST_NAME | LAST_NAME | TITLE        |
+------------+-----------+--------------+
| George     | Orwell    | 1984         |
| George     | Orwell    | Animal Farm  | <-- This author's books were all published before 1950
+------------+-----------+--------------+

Now the predicate is applied after the join operator, not as a part of the join operator, so it's just an ordinary predicate.

References to this page

Feedback

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

The jOOQ Logo