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;

Here is the question for you – why is it giving answers as zero? Please leave your answer in a comment below.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

21 thoughts on “SQL Puzzle – A Quick Fun with Bit Wise Operator

  1. 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)

  2. 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

  3. 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.

  4. 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.

  5. 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..

  6. 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

    • 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)

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

  8. 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

  9. 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

  10. 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;
    }

  11. 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

  12. 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

  13. Pingback: SQL Puzzle – A Quick Fun with Bit Wise Operator – Part 2 | Journey to SQL Authority with Pinal Dave

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s