SQL and logical operators and null checks

Question

I've got a vague, possibly cargo-cult memory from years of working with SQL Server that when you've got a possibly-null column, it's not safe to write "WHERE" clause predicates like:

 ... WHERE the_column IS NULL OR the_column < 10 ...

It had something to do with the fact that SQL rules don't stipulate short-circuiting (and in fact that's kind-of a bad idea possibly for query optimization reasons), and thus the "<" comparison (or whatever) could be evaluated even if the column value is null. Now, exactly why that'd be a terrible thing, I don't know, but I recall being sternly warned by some documentation to always code that as a "CASE" clause:

 ... WHERE 1 = CASE WHEN the_column IS NULL THEN 1 WHEN the_column < 10 THEN 1 ELSE 0 END ...

(the goofy "1 = " part is because SQL Server doesn't/didn't have first-class booleans, or at least I thought it didn't.)

So my questions here are:

1. Is that really true for SQL Server (or perhaps back-rev SQL Server 2000 or 2005) or am I just nuts?
2. If so, does the same caveat apply to PostgreSQL? (8.4 if it matters)
3. What exactly is the issue? Does it have to do with how indexes work or something?

My grounding in SQL is pretty weak.

Answer 1

Instead of

the_column IS NULL OR the_column < 10

I'd do

isnull(the_column,0) < 10

or for the first example

WHERE 1 = CASE WHEN isnull(the_column,0) < 10 THEN 1 ELSE 0 END ...