Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
HAVING clause
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The HAVING clause is commonly used to further restrict data resulting from a previously issued GROUP BY clause. An example, selecting only those authors that have written at least two books:
SELECT AUTHOR_ID, COUNT(*) FROM BOOK GROUP BY AUTHOR_ID HAVING COUNT(*) >= 2
create.select(BOOK.AUTHOR_ID, count())
.from(BOOK)
.groupBy(AUTHOR_ID)
.having(count().ge(2))
.fetch();
According to the SQL standard, you may omit the GROUP BY clause and still issue a HAVING clause. This will implicitly GROUP BY (). jOOQ also supports this syntax. The following example selects one record, only if there are at least 4 books in the books table:
SELECT COUNT(*) FROM BOOK HAVING COUNT(*) >= 4
create.select(count(*))
.from(BOOK)
.having(count().ge(4))
.fetch();
Feedback
Do you have any feedback about this page? We'd love to hear it!