Hi,
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.
I am not MSSQL person, but I ran into the same problem with Oracle. The trivial answer is, because Boolean is not a valid column type in those databases. Now, why they decided you don't need Booleans as values is anybody's guess.
@paxdiablo, that's so missing the point... The OP's example is just a minimal example. This is still simplistic but real-world example: Consider a People table, containing names and ages. You want to get all the people, but also want to know if they are underage. In both MySQL and PostgreSQL, you can write
SELECT name, age < 18 AS minor FROM people
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.
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.
The expression price < 500 returns a logical value: TRUE, FALSE or UNKNOWN. It is not a data value, which is why you need to use a CASE expression to return a corresponding data value.
FWIW the Microsoft Access Database Engine does indeed treat the results of expressions as data values e.g. you can ask all kinds of wacky questions such as:
SELECT 1 = 1, 1 = NULL, 1 <> NULL, 1 IN (NULL)
FROM Foo;
...and it will happily provide answers but of course this merely proves that Access does not implement the SQL language!