Simple where clause condition involving NULL

Question

I have a query that needs to exclude both Null and Blank Values, but for some reason I can't work out this simple logic in my head.

Currently, my code looks like this:

WHERE [Imported] = 0 AND ([Value] IS NOT NULL **OR** [Value] != '') 

However, should my code look like this to exclude both condition:

WHERE [Imported] = 0 AND ([Value] IS NOT NULL **AND** [Value] != '') 

For some reason I just can't sort this in my head properly. To me it seems like both would work.

Answer 1

In your question you wrote the following:

have a query that needs to exclude both Null and Blank Values

So you have answered yourself, the AND query is the right query:

WHERE [Imported] = 0 AND ([Value] IS NOT NULL AND [Value] != '') 

Here is an extract from the ANSI SQL Draft 2003 that I borrowed from this question:

6.3.3.3 Rule evaluation order

[...]

Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.

You don't specify what kind of database system you are using but the concept of short-circuit evaluation which is explained in the previous paragraph applies to all major SQL versions (T-SQL, PL/SQL etc...)

Short-circuit evaluation means that once an expression has been successfully evaluated it will immediately exit the condition and stop evaluating the other expressions, applied to your question:

If value is null you want to exit the condition, that's why it should be the first expression (from left to right) but if it isn't null it should also not be empty, so it has to be NOT NULL and NOT EMPTY.

This case is a bit tricky because you cannot have a non empty string that is also null so the OR condition will also work but you will do an extra evaluation because short-circuit evaluation will never exit in the first expression:

Value is null but we would always need to check that value is also not an empty string (value is null or value is not an empty string).

In this second case, you may get an exception because the expression [Value] != '' may be checked on a null object.

So I think AND is the right answer. Hope it helps.

Answer 2

If the value was numeric and you didn't want either 1 or 2, you would write that condition as

... WHERE value != 1 AND value != 2

An OR would always be true in this case. For instance a value of 1 would return true for the check against 2 - and then the OR-check would return true, as at least one of the conditions evaluated to true.

When yu also want to check against null values, the situation is a bit more complicated. A check against a null value always fails: value != '' is false when value is null. That is why there is a special IS NULL or IS NOT NULL test.