# SQL Puzzle – A Quick Fun with Bit Wise Operator

It has been a long time since we have puzzle on this blog so let us have a quick puzzle this Friday. It is based on Bit Wise Operator and very simple if you know those operators.

We will guess the output of the SQL script.

For example, here is the select statement:

`SELECT 10 & 10 ` Upon executing it will return us answer as a 10.

However, when I execute following two queries, both of the queries give an answer as Zero.

`SELECT 10 & 0; SELECT 1 & 100;` Reference: Pinal Dave (https://blog.sqlauthority.com)

#### Related Posts

• Dale R
February 7, 2014 7:31 am

1010 & 0000 = 0
1100100 & 0000001 = 0

• patelriki13
February 7, 2014 7:44 am

Because bit wise & operation is zero.

e.g. 1
1: 1010
100: 1010
—————
& : 1010 (i.e. 10)

e.g. 2
10: 1010
0: 0000
————–
& : 0000 (i.e. 0)

e.g. 3
1: 0000001
100: 1100100
—————
& : 0000000 (i.e. 0)

• Ronald
February 7, 2014 7:50 am

It’s interesting.

Binary number operation
SELECT 10 & 0
=> 1010 & 0000 in binary => 0000
SELECT 1 & 100
=> 0000001 & 1100100 in binary => 0000000

Another example is “SELECT 11 & 14; ”
=> 1011 & 1110 in binary => 1010 in binary => 10

• Julie
February 7, 2014 8:03 am

It returns zero because of the logical & operation. The first and second bit in each position is not 1. For example: SELECT 111 & 111 equals 111 because each bit in each position is 1, also SELECT 10 &100 equals 0 because the first and second bit in each position is not 1.

• Ivan
February 7, 2014 8:13 am

bin Dec
00000000 = 0
00000001 = 1
00001010 = 10
01100100 = 100

“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.”

So, we also can try “10 & 100” and result well be 0.

• Ajay Gopal Shrestha
February 7, 2014 8:33 am

Its bitwise and operator..
100
001
Anding from left to right as we do while adding when we were kid.. Gives..
000

And operator works as
0 & 0 is 0
0& 1 is 0
1 & 0 is 0
1 & 1 is 1

Nice trick Sir.. I was wondering what would be a real world scenario of using it..

• Kumar Harsh
February 7, 2014 12:25 pm

sometime you have enum define and you need to display data in report which is not in .net.
For example

• ajay
February 7, 2014 8:55 am

SQL Server always stores Numbers in “Right” precedence.
Ex: 1 & 10 is actually takes the values into buffers as 00000001 and 00000010 . so, if we apply a bit AND the result is 00000000

• Dharni D
February 7, 2014 8:56 am

10 & 00 = 00
001 & 100 = 000
Applying ‘&’ operator on each bit, you get zeroes.

• Slava Murygin
February 7, 2014 9:05 am

100 = 01100100
1 = 00000001

1100100 & 1 = zero in common

• Kunal Mandloi
February 7, 2014 10:06 am

its a bit wisae operation and its tration like and operator
10 & 10 both condition are true it will return 1(10)
and for rest it will false ie (0)

• Parth Malhan
February 7, 2014 11:51 am

In TSQL bitwise AND (&) operator takes integer values and Convert it into Binary Values by self
And then does bitwise comparison on these 2 Expressions.

AS in example given by you
select 10&10
will be translated as
Select 1010&1010
And the result will be
1010 and then this result is again translated to integer values which is 10

Whether in case of
Select 1&100

this will be translated as
Select 1&1100100 will result in 0 and this will be translated to Integer as 0

• Pooyan
February 7, 2014 11:55 am

10 in binary format equals by 1010
When you perform the bitwise with 0 you get 0
Same with 100 which it’s right most number in binary format is 0 so the result of the logical and with any other number would be 0

• Alex Elesus
February 7, 2014 1:48 pm

Guys, you know it not!
In the first example we need to multiply: 10 x 0 = 0
In the second example we just remove heading “1” from both parts: 100=>00, 1 => 0; now multiply 00 x 0 = 0
Much better explanation than all that bitwisemagic :D

• vijaykumar kagne
February 7, 2014 3:35 pm

select 10 & 10
here we are comparing the same value it means simple
if(T&T)//it will return true value
{
select T;
}
else
{
select F;
}

select 0 & 1
here we are comparing the same value it means simple
if(F&T)//it will return false value
{
select T;
}
else
{
select F;
}

select 100 & 0
here we are comparing the same value it means simple
if(F&T) //it will return false value
{
select T;
}
else
{
select F;
}

• mayank
February 7, 2014 8:41 pm

The binary representation of 1 is 0000 0000 0000 0001. The binary representation of 10 is 0000 0000 0000 1010. Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 0000, which is decimal 11.

(A & B)
0000 0000 0000 0001
0000 0000 0000 1010
——————-
0000 0000 0000 0000 = 0

The binary representation of 1 is 0000 0000 0000 0001. The binary representation of 10 is 0000 0000 0110 0100 . Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 0000, which is decimal 11.

(A & B)
0000 0000 0000 0001
0000 0000 0110 0100
——————-
0000 0000 0000 0000 = 0

• Purva
February 7, 2014 9:59 pm

The & operator does a bitwise AND on both the integer values by converting them to their binary form.
For eg SELECT 1 & 100;

00000001 —- binary value of 1
01100100 —- binary value of 100
—————-
00000000 — (After applying the AND operator) decimal value of 0

For select 10 & 10

00001010 — binary value of 10
00001010 — binary value of 10
————–
00001010 — (After applying the AND operator) decimal value of 10

• Purva
February 7, 2014 10:00 pm

Small correction – I meant the output is binary value of 0 & 10 respectively

• Jaffar
February 7, 2014 10:30 pm

binary value for 1 and 0 is always 0000 which makes the result 0 (with AND operation)

Please let me know whether this is right?

• Dilip Kshirsagar
July 9, 2014 7:07 pm

select 10 & 0
select 10 & 100
10= 00001010
0= 00000000
——————
00000000
Sum of this =0

10= 00001010
100=01100100
——————
00000000
Sum of this =0