1 and 0 not recognized as boolean constants in T-SQL

Question

If I try to run this query in SQL Server 2005:

SELECT 1 WHERE NOT ( 0 )

I get this error:

Msg 4145, Level 15, State 1, Line 1 An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Sometimes when debugging complex WHERE statements I will cut out pieces and test a particular scenario by inserting something like 1=1 for true. I would prefer to just use 1 or TRUE but this doesn't work in the above case. Why is it not able to evaluate NOT( 0 ) as a boolean expression?

Edit: This does work fine, showing that I don't have to add a column:

SELECT 1 WHERE NOT(1<>1)

Answer 1

because the WHERE clause is separate from the SELECT clause

you can select completely different things in the SELECT that are not even in in the WHERE clause

for example

select name, address, state, company, country
from bla
where occupation='programmer'
and hobby ='debugging'

so where not (0) would mean what in this case, you need to add the column

AND RegionID <> 0

or

AND RegionID NOT IN (0) --althoug if you have NULLS in coumn this won't work

Answer 2

0 and 1 are integers. They are not themselves boolean expressions and in SQL only expressions can have a value of TRUE, FALSE or UNKNOWN. Saying "NOT 7.5" for example is not itself a boolean expression. In actuality, what you are trying to say "NOT (7.5 <> 0)" Thus, in your example, you would need to convert your value to an expression:

Select 1 Where Not ( 0 <> 0 )

In T-SQL at least, there are no constants that you can use for TRUE and FALSE. I.e., there is no means to query for :

Select 1 Where Not TRUE

Answer 3

SELECT 1 WHERE NOT ( 0 ) 

In the where clasue you have to have a comparison, where what = what, where what <> what etc. You can't have a comparision with only one value. It needs to know what you are trying to compare the 0 to.

Answer 4

0 and 1 are not boolean constants, they're bit constants.