[BeefWellington]

I've seen many devs extrapolate this thinking too far into sending only the most simple queries and doing all of the record filtering on the application end. This isn't what I think you're saying -- just piggybacking to try and explain further.

The key thing here is to understand that you want the minimal correct query for what you need, not to avoid "making the database work".

The given example is silly because there's additional parameters that must be either NULL or have a value before the query is sent to the DB. You shouldn't send queries like:

    SELECT \* FROM users
    WHERE id = 1234
        AND (NULL IS NULL OR username = NULL)
        AND (NULL IS NULL OR age > NULL)
        AND (NULL IS NULL OR age < NULL)

But you should absolutely send:

    SELECT \* FROM users
    WHERE id = 1234
        AND age > 18
        AND age < 35

[deredede]

While sub-optimal, your first example is probably fine to send and I'd expect to be simplified early during query planning at a negligible cost to the database server.

What you shouldn't send is queries like:

    SELECT \* FROM users
    WHERE ($1 IS NULL OR id = $1)
        AND ($2 IS NULL OR username = $2)
        AND ($3 IS NULL OR age > $3)
        AND ($4 IS NULL OR age < $4)

because now the database (probably) doesn't know the value of the parameters during planning and needs to consider all possibilities.

[oever]

Interesting. I try to use prepared statements to avoid redoing the planning. But since the database schema is small compared to the data, the cost of query planning is quickly negligible compared to running an generic query that is inefficient.

[tsarchitect]

One of my rules: don't send NULL over the wire, but of course always check for NULL on the server if you're using db functions.