SQL Query include row when at Least one of three fields is not null-CodePudding

I would like to include a record in my query when, from a certain three fields, at least one of the three is not null.

I'm probably overthinking this, as there are other "AND" clauses that need to be present.

Do I just need to use parentheses? This is a Presto query by the way, if that helps.

CodePudding user response：

The most straightforward way would be to test all three columns directly with an or operator between them. As you noticed, you should surround the entire condition with parentheses to avoid bugs related to the fact that the and logical operator in other conditions has a higher precedence:

(col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL)

A more elegant solution, however, could be to coalesce all three columns and check the result:

COALESCE(col1, col2, col3) IS NOT NULL

CodePudding user response：

That is simple. Use parentheses when mixing AND and OR:

WHERE (col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL)
AND ...

CodePudding user response：

You can check each column with IS NOT NULL:

col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL

Or:

NOT (col1 IS NULL AND col2 IS NULL AND col3 IS NULL)

Or with COALESCE():

COALESCE(col1, col2, col3) IS NOT NULL