Implicit path correlation
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A special case of the putting path expressions in the FROM clause is the implicit path correlation, where a path establishes a correlation to an outer query, rather than a join to a previous table from the FROM clause. This correlated subquery case is very powerful, e.g. to count books per author:
SELECT
AUTHOR.ID, (
SELECT COUNT(*) FROM BOOK
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
)
FROM AUTHOR
// Count the number of books per author
create.select(
AUTHOR.ID,
field(selectCount().from(AUTHOR.book())))
.from(AUTHOR)
.fetch();
Or as a way to simplify a correlate a MULTISET subquery:
// Get all books by author
create.select(
AUTHOR.ID,
multiset(selectFrom(AUTHOR.book())))
.from(AUTHOR)
.fetch();
Feedback
Do you have any feedback about this page? We'd love to hear it!