# How the BIT_AND() function works in Mariadb?

MariaDB’s `BIT_AND()`

function is used to perform a bitwise AND operation on all bits in a given expression.

The MariaDB `BIT_AND()`

function is used to perform a bitwise AND operation on all bits in a given expression. This function is particularly useful in scenarios where you need to combine bits from multiple rows to find commonalities, such as permissions settings or feature flags.

## Syntax

The syntax for the MariaDB `BIT_AND()`

function is as follows:

```
BIT_AND(expr) [over_clause]
```

This function takes an expression `expr`

as an argument and performs the bitwise AND operation. It operates with 64-bit (BIGINT) precision, making it suitable for large datasets. The optional `over_clause`

allows `BIT_AND()`

to be used as a window function.

## Examples

### Example 1: Basic Usage

This example demonstrates the basic usage of `BIT_AND()`

without any tables:

```
SELECT BIT_AND(3);
```

```
+------------+
| BIT_AND(3) |
+------------+
| 3 |
+------------+
```

The output is `3`

because the bitwise AND of `3`

(which is `011`

in binary) with itself is `011`

.

### Example 2: With Multiple Numbers

Here we’ll see how `BIT_AND()`

works with multiple numbers:

```
SELECT BIT_AND(3 & 1);
```

```
+----------------+
| BIT_AND(3 & 1) |
+----------------+
| 1 |
+----------------+
```

The output is `1`

because the bitwise AND of `3`

(`011`

in binary) and `1`

(`001`

in binary) is `001`

.

### Example 3: No Rows Match

This example shows what happens when no rows match:

```
SELECT BIT_AND(NULL);
```

```
+----------------------+
| BIT_AND(NULL) |
+----------------------+
| 18446744073709551615 |
+----------------------+
```

When no rows match, `BIT_AND()`

returns a value with all bits set to `1`

, which is `18446744073709551615`

in a 64-bit system.

### Example 4: Using with Tables

To demonstrate `BIT_AND()`

with tables, we’ll create a simple table:

```
DROP TABLE IF EXISTS example;
CREATE TABLE example (a INT);
INSERT INTO example VALUES (3), (1), (7);
```

Now, let’s use `BIT_AND()`

:

```
SELECT BIT_AND(a) FROM example;
```

```
+------------+
| BIT_AND(a) |
+------------+
| 1 |
+------------+
```

The output is `1`

because the bitwise AND of `3`

(`011`

), `1`

(`001`

), and `7`

(`111`

) is `001`

.

### Example 5: Window Function

Here’s how to use `BIT_AND()`

as a window function:

```
DROP TABLE IF EXISTS example;
CREATE TABLE example (a INT, b INT);
INSERT INTO example VALUES (3, 1), (1, 1), (7, 2);
SELECT a, BIT_AND(a) OVER (PARTITION BY b) FROM example;
```

```
+------+----------------------------------+
| a | BIT_AND(a) OVER (PARTITION BY b) |
+------+----------------------------------+
| 3 | 1 |
| 1 | 1 |
| 7 | 7 |
+------+----------------------------------+
```

The `BIT_AND()`

function is applied to each partition of column `b`

, resulting in `1`

for the first partition and `7`

for the second.

## Related Functions

Here are a few functions related to MariaDB’s `BIT_AND()`

:

- MariaDB’s
`BIT_OR()`

function is used to perform a bitwise OR operation on all bits in an expression. - MariaDB’s
`BIT_XOR()`

function is used to perform a bitwise XOR operation on all bits in an expression. - MariaDB’s
`BIT_COUNT()`

function is used to count the number of bits that are set in an expression.

## Conclusion

The `BIT_AND()`

function in MariaDB is a powerful tool for performing bitwise operations across multiple rows. Its ability to be used as an aggregate or window function adds to its versatility in handling various data processing tasks. Understanding how to use `BIT_AND()`

and related functions can greatly enhance your data manipulation capabilities in MariaDB.