SQL Bitwise Operators

Bitwise operators allow you to perform operations at the binary level on integer values. They are especially useful for flags, permissions, or status indicators stored as numbers.

1️⃣ Common Bitwise Operators in SQL

| Operator | Description                  | Example         |
|----------|------------------------------|----------------|
| `&`      | Bitwise AND                  | `5 & 3` → `1`  |
| `|`      | Bitwise OR                   | `5 | 3` → `7`  |
| `^`      | Bitwise XOR (exclusive OR)   | `5 ^ 3` → `6`  |
| `~`      | Bitwise NOT (invert bits)    | `~5` → `-6`    |
| `<<`     | Left shift (multiply by 2^n) | `5 << 1` → `10`|
| `>>`     | Right shift (divide by 2^n)  | `5 >> 1` → `2` |

2️⃣ Understanding Bitwise Operators

Example: 5 in binary → 0101

Example: 3 in binary → 0011

| Operation | Binary Calculation | Result (Decimal) |
|-----------|------------------|-----------------|
| `5 & 3`   | 0101 & 0011      | 0001 → 1        |
| `5 | 3`   | 0101 | 0011      | 0111 → 7        |
| `5 ^ 3`   | 0101 ^ 0011      | 0110 → 6        |
| `~5`      | ~0101            | 1010 → -6*      |
| `5 << 1`  | 0101 << 1        | 1010 → 10       |
| `5 >> 1`  | 0101 >> 1        | 0010 → 2        |

*Note: ~ in SQL uses two’s complement, so ~5 = -6.

3️⃣ Using Bitwise Operators in SQL

Suppose we have a Permissions table with flags:

| UserID | Permissions |
|--------|------------|
| 1      | 5          |
| 2      | 3          |
| 3      | 7          |

• Permissions as bits:
• Read = 1 (0001)
• Write = 2 (0010)
• Execute = 4 (0100)

a) Check if a user has a Write permission

SELECT UserID
FROM Permissions
WHERE Permissions & 2 = 2;

Output:

| UserID |
|--------|
| 2      |
| 3      |

Explanation: Users 2 and 3 have the Write bit set.

b) Add Execute permission to a user

UPDATE Permissions
SET Permissions = Permissions | 4
WHERE UserID = 2;

• Bitwise OR | adds a bit without removing existing ones.

c) Remove Write permission from a user

UPDATE Permissions
SET Permissions = Permissions & ~2
WHERE UserID = 3;

• Bitwise AND with NOT (& ~) clears a specific bit.

d) Toggle Read permission

UPDATE Permissions
SET Permissions = Permissions ^ 1
WHERE UserID = 1;

• XOR (^) flips the bit: 0 → 1, 1 → 0

4️⃣ Beginner Tips

• Use bitwise operators when storing multiple Boolean flags in a single integer.
• & → check a bit, | → set a bit, ^ → toggle a bit, ~ → invert all bits
• Combine with WHERE to filter users by permissions.

-- Check if user has Write permission (2)
SELECT UserID
FROM Permissions
WHERE Permissions & 2 = 2;

-- Add Execute permission (4) to UserID = 2
UPDATE Permissions
SET Permissions = Permissions | 4
WHERE UserID = 2;

-- Remove Write permission (2) from UserID = 3
UPDATE Permissions
SET Permissions = Permissions & ~2
WHERE UserID = 3;

-- Toggle Read permission (1) for UserID = 1
UPDATE Permissions
SET Permissions = Permissions ^ 1
WHERE UserID = 1;