Is there any reason why you cannot select a statement as a bit in SQL Server?

Question

I am wondering why the following fails:

SELECT price<500 as PriceIsCheap

and forces you to do the following:

SELECT CASE WHEN (price<500) THEN 1 ELSE 0 END as PriceIsCheap

When, as per the answer to this related question, the conversion table says that an implicit conversion should occur.

Answer 1

There is no boolean data type in SQL, BIT is kind of a hack, but the main problem is that due to the SQL concept of NULL true boolean logic is impossible (for example, what would your query return if price was NULL?)

Note that I'm not saying that there are not possible ways to implement boolean logic that "mostly" work (for example, you could say that TRUE OR NULL is NULL or whatever) just that the people who designed the SQL standard couldn't decide on The One True Representation for boolean logic (for example, you could also argue that TRUE OR NULL is TRUE, since TRUE OR <anything> is TRUE).

The boolean expressions (=, <=, >=, etc) are only valid in certain places (notably, WHERE clauses and CASE labels) and not in any other place.

Answer 2

Well you'll also find you can't if you have a bit column called IsCheap do SELECT * FROM STUFF WHERE IsCheap, you have to do WHERE IsCheap=1.

The reason is simple, the data type is a bit, not a bool. True, it's basically the only use you'll put it to and it's implicitly converted by almost any data access framework, but it's still technically a bit with 0 or 1 rather than a bool with true or false. There's an obvious connection we can all see, but SQL wasn't written with this assumption in it so we have to provide the logic to convert true/false to 1/0.