So I end up having values in the actual column that may be complete junk and I need to check another column to know that? I would expect that to cause trouble sooner or later.
But at the same time, proper languages that have operator overriding and custom types make that kind of thing trivial - look at how many languages have a Maybe<T> these days, which is basically automating that process.
The fact that doing it in SQL would be suicidal shows that, while SQL was a brilliant language for the '70s... it has not evolved nearly enough.