SQL Bitwise AND Operator

SQL Bitwise AND Operator

The SQL & (Bitwise AND) operator is used to perform a bitwise AND operation between two integer values at the binary level. It compares each bit of the first operand with the corresponding bit of the second operand and returns a value where both bits are 1.

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

---

Syntax of SQL Bitwise AND Operator

The basic syntax of the SQL Bitwise AND operator is:

SELECT number1 & number2 AS result;

Explanation:

• The & operator performs a bitwise AND operation between two integers.
• Each bit in both numbers is compared; only if both bits are 1, the result will have 1 in that position.
• The result is returned as an integer.

---

Step-by-Step Examples Using SQL Bitwise AND Operator

1 Basic Example of Bitwise AND

Let’s perform a bitwise AND operation between 5 and 3:

SELECT 5 & 3 AS result;

Explanation:

• 5 in binary is 101.
• 3 in binary is 011.
• Performing a bitwise AND (&):
  

        101  (5)
      & 011  (3)
      ---------
        001  (1)
      

  
  
• The result is 1.

---

2 Using Bitwise AND in a Table Query

Let’s create an employees table to store employee IDs and permissions using bitwise flags:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    permissions INT
);

Insert some sample data:

INSERT INTO employees (name, permissions)
VALUES 
('Arjun', 5),  -- Binary: 101
('Ram', 3),    -- Binary: 011
('John', 7),   -- Binary: 111
('Priya', 2);  -- Binary: 010

Now, let’s retrieve employees who have a specific permission using the Bitwise AND operator. Suppose we want to check who has permission flag 1 (binary 001):

SELECT name, permissions 
FROM employees 
WHERE permissions & 1 = 1;

Explanation:

• permissions & 1 = 1 checks if the least significant bit is set.
• Only employees whose permission value has 1 in the last bit position are selected.
• Results:
  • Arjun (5 = 101 in binary) → Matches
  • Ram (3 = 011 in binary) → Matches
  • John (7 = 111 in binary) → Matches
  • Priya (2 = 010 in binary) → Does not match

---

Conclusion

In this SQL Bitwise AND operator tutorial, we covered:

1. The concept of the Bitwise AND operator.
2. Syntax and basic usage.
3. Example calculations using the operator.
4. A real-world example involving permissions in a database.