[thelist] (SQL2k) NULL and LIKE

[Paul Cowan]

Tab Alleman wrote:
> So, NULL is LIKE '%RedAlert%'???  How so?  Is this expected behavior?

No, NULL is not LIKE '%RedAlert%'. But it's not NOT LIKE '%RedAlert%',
either.

Tri-value boolean logic is a tricky thing. Remember, 'null' isn't a
'special value', as some people think of it -- it's an _absence_ of a
value. Or, even better, it's an _unknown_ value. Whenever you see
'null', think 'unknown value'

Is 'unknown value' LIKE '%RedAlert%'? We don't know. We can't know, because
we don't know what the value is. It might be, it might not be. So the
answer isn't 'yes', or 'no', it's 'unknown'.

Conversely, is 'unknown value' NOT LIKE '%RedAlert%'? We don't know,
for the same reason. Therefore, that evaluates to 'unknown' too.

When it comes to logical operators, it's even more fun. What's 'true
AND null'? What about 'false AND null'? What about 'true OR null', or
'false AND null'?

Hours of fun for the whole family.

Paul

PS: null, false, true, and null, respectively.