Check if more than N rows are returned

Counting the rows that match a WHERE condition can be slow, and may return a very high and precise number that is useful to no one. Let’s see how to just check if a query returns more than N rows.

The problem

Consider the following query:

SELECT count(*) FROM product WHERE size < 100 AND price < 1000;

We could output the result to the user:

Your search has returned 15 results.

This message can be useful. But if the search returns 97,226 rows, the query could be slow, and the user will have no interest in knowing such an exact number. Instead, let’s just output:

Your search has returned more than 100 results.

The solution

Here’s how to do it:

SELECT count(*)
    FROM (
    SELECT id
        FROM product
        WHERE size < 100 AND price < 1000
        LIMIT 101
);

The nested query will read at most 101 rows, in which case our application can write “your search returned more than 100 rows”. This means that the query will be faster.

The outer query will simply count the rows found by the nested query.

Depending on the DBMS you use, you may need to use FETCH FIRST 101 ROWS ONLY instead of LIMIT 101.