In DuckDB, the bit_and() function returns the bitwise AND of all bits in a given expression.

Let’s take a quick look at the bit_and() function, along with some simple examples.

Syntax

The syntax goes like this:

bit_and(arg)

Where arg is the target column or expression that the function operates on.

It’s also possible to provide an over clause to make the function execute as a window function.

Example

Suppose we create the following table and insert data:

CREATE TABLE flag_data (
    ref INTEGER,
    flag INTEGER
);

INSERT INTO flag_data VALUES
    (1, 15),  -- 1111 in binary
    (1, 13),  -- 1101 in binary
    (1, 14),  -- 1110 in binary
    (2, 7),   -- 0111 in binary
    (2, 6);   -- 0110 in binary

Here, the flag column contains integer values. The comments show the 4-bit binary equivalent for the respective value.

Now let’s use bit_and() to return the bitwise AND of all bits in that column:

SELECT 
    bit_and(flag)
FROM flag_data;

Result:

+---------------+
| bit_and(flag) |
+---------------+
| 4             |
+---------------+

We can pass bit_and() to the bin() function in order to get the binary representation:

SELECT 
    bit_and(flag),
    bin(bit_and(flag)) as binary_representation
FROM flag_data;

Result:

+---------------+-----------------------+
| bit_and(flag) | binary_representation |
+---------------+-----------------------+
| 4             | 100                   |
+---------------+-----------------------+

Grouped Data

Here it is grouped by the ref column:

SELECT ref, 
       bit_and(flag) as common_flags,
       bin(bit_and(flag)) as binary_representation
FROM flag_data
GROUP BY ref;

Result:

+-----+--------------+-----------------------+
| ref | common_flags | binary_representation |
+-----+--------------+-----------------------+
| 1   | 12           | 1100                  |
| 2   | 6            | 110                   |
+-----+--------------+-----------------------+

Window Function

We can provide an OVER clause in order to run it as a window function:

SELECT 
    ref,
    bit_and(flag) OVER (ORDER BY ref) AS bit_and
FROM flag_data;

Result:

+-----+---------+
| ref | bit_and |
+-----+---------+
| 1   | 12      |
| 1   | 12      |
| 1   | 12      |
| 2   | 4       |
| 2   | 4       |
+-----+---------+

For a more detailed example, let’s create a new table with employee permissions data:

-- Create a table to store user permissions by department
CREATE TABLE employee_permissions (
    employee_id INTEGER,
    department_id INTEGER,
    hire_date DATE,
    -- Using integer to store permission flags where:
    -- 1 (001): Read
    -- 2 (010): Write
    -- 4 (100): Admin
    permission_flags INTEGER
);

-- Populate with sample data
INSERT INTO employee_permissions VALUES
    (1, 1, '2023-01-01', 1),  -- Read only
    (2, 1, '2023-02-15', 3),  -- Read + Write
    (3, 1, '2023-03-10', 7),  -- Read + Write + Admin
    (4, 2, '2023-01-05', 1),  -- Read only
    (5, 2, '2023-04-20', 2),  -- Write only
    (6, 2, '2023-06-01', 4),  -- Admin only
    (7, 3, '2023-07-01', 3),  -- Read + Write
    (8, 3, '2023-08-15', 5);  -- Read + Admin

Now let’s use bit_and() as a window function to identify the common (baseline) permissions that all employees in each department share:

SELECT 
    department_id,
    bit_and(permission_flags) as baseline_permissions,
    CASE bit_and(permission_flags)
        WHEN 7 THEN 'Full Access (R+W+A)'
        WHEN 3 THEN 'Standard Access (R+W)'
        WHEN 1 THEN 'Basic Access (R)'
        WHEN 0 THEN 'No Common Access'
        ELSE 'Custom Access'
    END as access_level,
    COUNT(*) as employee_count
FROM employee_permissions
GROUP BY department_id
ORDER BY department_id;

Result:

+---------------+----------------------+------------------+----------------+
| department_id | baseline_permissions |   access_level   | employee_count |
+---------------+----------------------+------------------+----------------+
| 1             | 1                    | Basic Access (R) | 3              |
| 2             | 0                    | No Common Access | 3              |
| 3             | 1                    | Basic Access (R) | 2              |
+---------------+----------------------+------------------+----------------+