AR7 AR7 - 1 year ago 49
SQL Question

Do you always need to use coalesce in a WHERE if there can be null/empty values in the columns you're comparing?

I know can't compare columns that are empty/null to others so often times I find myself writing a query like:

WHERE field2 <> '' AND coalesce(field1, '') <> coalesce(field2, '')

This is the way I've always done it, but I'm curious if there's a better way to handle null/empty values. Also I'm curious as to why
NULL <comparison operator> anything
in the first place.

Answer Source

No. The use of COALESCE() generally prevents indexes from being used. The equivalent syntax using boolean logic gets cumbersome.

The best solution is to use IS DISTINCT FROM:

where field1 is distinct from field2

By using built-in operators, there is a better change that Postgres can optimize the query. IS DISTINCT FROM is ANSI standard syntax which is explained in the documentation.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download