SQL Bitwise OR Operator

The SQL | (Bitwise OR) operator performs a bitwise OR operation between two integer values. This operator compares each bit of its operands and sets the corresponding bit in the result to 1 if at least one of the corresponding bits in the operands is 1. It is useful in scenarios involving bitmasking and permission handling.

In this tutorial, we will explore the SQL Bitwise OR Operator, its syntax, and practical examples.

Syntax of SQL Bitwise OR Operator

The basic syntax of the SQL | (Bitwise OR) operator is:

</>
Copy
SELECT number1 | number2 AS result;

Explanation:

  • number1 and number2 are the integer values on which the Bitwise OR operation is performed.
  • The result contains a number whose bits are set to 1 where at least one of the bits in number1 or number2 is 1.

Step-by-Step Examples Using SQL Bitwise OR Operator

1 Basic Example of Bitwise OR Operator

Let’s perform a Bitwise OR operation on two numbers:

</>
Copy
SELECT 5 | 3 AS result;

Explanation:

In binary representation:

5  =  101 (binary)
3  =  011 (binary)
-----------------
5 | 3 = 111 (binary) = 7 (decimal)

The result of 5 | 3 is 7, as the OR operation results in 1 wherever at least one operand has a 1 bit.

2 Using Bitwise OR in a Database Table

Let’s create a users table where each user has a permission level stored as an integer. The Bitwise OR operator will be used to update permission levels.

Step 1: Creating the Table

</>
Copy
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    permission INT
);

Step 2: Inserting Sample Data

</>
Copy
INSERT INTO users (name, permission)
VALUES 
('Arjun', 4),  -- Binary: 100
('Ram', 2),    -- Binary: 010
('John', 1),   -- Binary: 001
('Priya', 5);  -- Binary: 101

Step 3: Updating Permissions Using Bitwise OR

Let’s say we want to grant an additional permission (binary 010 = decimal 2) to all users.

</>
Copy
UPDATE users
SET permission = permission | 2;

Now, let us get the contents of users table.

</>
Copy
SELECT *
FROM users;

Explanation:

NamePrevious Permission (Binary)Updated Permission (Binary)Updated Permission (Decimal)
Arjun1001106
Ram0100102
John0010113
Priya1011117

The bitwise OR operation ensures that the required permission bit is always set without affecting other permissions.

Conclusion

The SQL Bitwise OR (|) operator is useful for handling bit-level operations in databases, particularly for permission management.

  1. It performs a bitwise OR operation between two numbers.
  2. It can be used for manipulating binary flags, user permissions, and bitwise calculations.
  3. Practical applications include access control and feature toggling.