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


Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo