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