The jOOQ User Manual Reference Don't do this SQL: COUNT(*) instead of EXISTS()

previous : next

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.

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...

The jOOQ User Manual Reference Don't do this SQL: COUNT(*) instead of EXISTS()

previous : next