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
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
SQL: COUNT(*) instead of EXISTS()
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Do you go to the supermarket to count all their apples just to see if they have any apples? You don't. Likewise, you shouldn't run a COUNT(*) query to check if the value is bigger than 0. Use the EXISTS predicate, instead.
Our pattern transformation feature can auto detect bad queries for you, e.g. COUNT(*) > 0 style queries or COUNT(expr) > 0 style queries.
We've blogged about this and benchmarked the difference. It really does make a difference!
So, don't do this:
if (create.fetchValue(selectCount().from(AUTHOR)) > 0) {
// ...
}
But do this, instead:
if (create.fetchValue(exists(selectOne().from(AUTHOR)))) {
// ...
}
Of course, it's totally possible to embed this EXISTS predicate in a more complex query and possibly avoid the unnecessary secondary roundtrip...
Feedback
Do you have any feedback about this page? We'd love to hear it!