CODEWITHSUNDEEP
sql-server-2005
alfoks
alfoks

Reputation: 4614

SQL Server boolean operators

I know that SQL Server does not have boolean data type and your best option is to use BIT data type.

My question is to what boolean expressions evaluate. For example if I have the statement

expr1 AND expr2

and if expr1 is true and expr2 is false, do they internally evaluate to BITs with values 1 and 0? And then the AND operator checks for BITs?

I think this is not the case because then the following should work:

select 1 where 1 and 0

So does sql server internally have a boolean data type?

The following work

select 1 where 1 = 0
select 1 where 1 = 0 and 0 = 0

but this

select 1 where 1 and 0

reports

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

which means that internally sql server handles the expressions as booleans, but why don't have access to that data type.

Upvotes: 1

Views: 5671

Answers (2)

Andriy M
Andriy M

Reputation: 77657

SQL Server does have a Boolean data type. You can open the Logical Operators (Transact-SQL) manual page and find the following statement:

Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

It's just that you can't use this type in the same way you can use other Transact-SQL data types. For instance, you can't declare boolean variables or arguments, add boolean columns to tables, cast to/from a boolean. But you can have boolean expressions and use them in contexts where they are required (WHERE, ON, check constraints…). You can also apply boolean operators to those expressions: AND, NOT et al. (Operators like <, =, LIKE and other can also be considered boolean, in the sense that they return boolean results, but their operands are actually never booleans.)

So, to summarise, there is a boolean type in SQL Server but its use is limited, as described above. Why? My answer may be a silly one, sorry, but that's one that I'm satisfied with: this is the way they chose it to be.

Upvotes: 3

YvesR
YvesR

Reputation: 6212

Logical operators results in true or false. So in your example you get a result if expr1 and expr2 is true. To check if its true or not is the operation you do for each expression.

So you compare e.g. a column against something like a bit value column_bit = 0 and this results in true or false. If your expr1 is true and expr2 is false your result is false for the logical AND operator.

Edit:

Your select 1 where 1 and 0 can't work because you don't use a compare operator. 1=1 AND 0=0 works e.g. but its nonsense :)

Upvotes: 0

Related Questions