# SQL Puzzle – A Quick Fun with Bit Wise Operator – Part 2

This is a follow up, puzzle of SQL Puzzle – A Quick Fun with BitWise Operator. Lots of people really like the earlier puzzle where I have used Bit Wise Operator AND and build a simple puzzle. Today we are going to use another bit wise operator – OR.

First execute following query:

SELECT 10 | 10

It will return us result as a 10.

Now execute following query:

SELECT 10 | 0

The above query will return us result as a 10 as well.

Now let us execute following query:

SELECT 1 | 100

It will return us result as a 101.

Here is the question back to you – Why does the last query return us result as a 101 when we have used Bit Wise Operator OR?

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

## 7 thoughts on “SQL Puzzle – A Quick Fun with Bit Wise Operator – Part 2”

1. Nithin Gangadharan |

‘|’ does ORing on binary representation of a given no.
E.g. 1. 10|10
1010
| 1010 = 1010 which is 10
2. 10|0
1010
| 0000 = 1010 which is again 10

Similarly 1|100
0000001
| 1100100 = 1100101 which is 101

Like

2. Ajay Gopal Shrestha |

Or table
0 | 0 = 0
0 | 1 = 1
1 | 0 = 1
1 | 1 = 1

100
001

Result: 101

Like

3. Parth Malhan |

Just Like your yesterday’s puzzle.
This bitwise OR (|) operator also takes integer values as Parameters and convert them to Binary by self, and do bit by bit comparison.

like:

SELECT 1|100
will be translated as
select
0000001
1100100
and returns
1100101 (101 in decimal)
and this will be translated as integer before displaying

Like

4. Mircea |

First of all, the result for last SELECT is wrong here, it is 101, not 10 as you show :)
Second, in the bitwise OR the rule is 1 | x = 1 for x either 0 or 1 and only 0 | 0 = 0.
100 has the last binary digit 0 (it divides by 2) and when we do 1 | 100 we get 101, because of the OR rule (1 | 0 = 1, 0 being the last binary digit of 100)

You can write this operation in binary mode:

1 | 1100100 = 1100101 = 101

Like

5. MP |

Pinal, your “Results” image for “SELECT 1 | 100″ is incorrect — it should show a result of “101” rather than a result of “10”. The answer is 101 because the bitwise-OR returns a 1 for each bit that is one in either of the expressions — it should be obvious that 0 | 100 would result in 100, and the first expression, 1, adds a bit to the ones column, hence a result of 101.

Like

6. Binary for 100 – 1100100

So 100 | 1 => 1100100 | 0000001 => 1100101 => 101

Like

7. Rakesh Lal Dewangan |

The logical OR operator is similar to AND as it works with two values. The difference is that where either of the bits at a specific location is set, the resultant bit will also be set. If both operands contain a zero at a position, the resultant bit will be clear. For example:
00000001 = 1
01100100 = 100
OR
01100101 = 101
So Select 1|100 would be 101.

Like