Difference between “and” and “&”

Question

I'm trying to understand the order of precedence for logical operations and have the following code:

declare @T bit ='TRUE'
declare @F bit ='False'

print @T and @F

and it returns an error as

Incorrect syntax near the keyword 'and'.

I replaced 'and' with '&', and the code works again. Why didn't the former code work? I'm using SQL server.

Answer 1

print @T & @F

Returns 0

& is the Bitwise AND operator.

The & bitwise operator performs a bitwise logical AND between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if and only if both bits (for the current bit being resolved) in the input expressions have a value of 1; otherwise, the bit in the result is set to 0.

In your case @T & @F resolves to 1 & 0 and so returns a result of datatype BIT with value 0

When passed to the PRINT operator this bit result is implicitly cast to string and the result output to the client.

---

print @T and @F

Has quite a lot wrong with it.

AND

Combines two Boolean expressions and returns TRUE when both expressions are TRUE

bit is not the same as boolean. They are not interchangeable and SQL Server won't implicitly cast bit to a boolean datatype when needed (SQL Server does not implement the SQL Boolean datatype.).

So you would need to use an expression like

@T = 'TRUE' AND @F = 'TRUE'

instead of

@T and @F

Even then your problems aren't over - PRINT doesn't accept a boolean expression anyway. You could use the expression in CASE as below.

PRINT CASE 
          WHEN     (@T = 'TRUE' AND @F = 'TRUE') THEN 'True' 
          WHEN NOT (@T = 'TRUE' AND @F = 'TRUE') THEN 'False' 
          ELSE 'Unknown' -- SQL uses three valued logic
      END