1

In the WHERE clause I have the following:

WHERE name<>''

I am wondering whether it makes any sense to add AND name IS NOT NULL?

My testing suggests that <>'' auto handles NULL because NULL<>'' is false (that is - using a NULL in comparison always returns false).

So when does it make sense to write: WHERE name<>'' AND name IS NOT NULL ?

variable
  • 8,262
  • 9
  • 95
  • 215
  • `because NULL<>'' is false` - no, `NULL<>''` is [`UNKNOWN`](https://stackoverflow.com/a/41180794/11683). – GSerg Jan 31 '22 at 07:32
  • 2
    @GSerg ...And UNKNOWN is not TRUE ! – SQLpro Jan 31 '22 at 08:24
  • 1
    I believe very occasionally a redundant predicate such as this can assist with indexed view matching and filtered index matching. If that is relevant you should test if it makes a difference by examining the execution plan – Charlieface Jan 31 '22 at 09:59

1 Answers1

3

When does it make sense to include WHERE name<>'' AND name IS NOT NULL

It is never required in that context, If Name is NULL then name<>'' evaluates to UNKNOWN.

The WHERE clause only returns rows where the predicate evaluates to true and rejects rows where it evaluates to false or unknown.

Even though it is not required you could maybe make a case for including the redundant predicate for clarity if the code will be maintained by generalist developers that are maybe not too familiar with all the vagaries of the above (though I would rather add a comment for these people then add redundant code).

A circumstance where the expression could make sense would be in a check constraint.

Check constraints reject rows where the expression is false and accepts ones where it is true or unknown

So the expression name<>'' AND name IS NOT NULL would reject NULL values in this case whereas name<>'' would allow them.

But even in this case it would be best just to declare the column as NOT NULL.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I believe very occasionally a redundant predicate such as this can assist with indexed view matching and filtered index matching. Might be wrong but that's what I remember... – Charlieface Jan 31 '22 at 10:00