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

Here are some basic examples that demonstrate how the bit_xor() function works in DuckDB.

Syntax

The syntax goes like this:

bit_xor(arg)

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

Basic Example

Here’s a simple example to demonstrate how the function works:

SELECT 
    bit_xor(value) AS result
FROM (VALUES (1), (2)) AS t(value);

Output:

+--------+
| result |
+--------+
| 3      |
+--------+

Explanation:

• This query applies bit_xor() to the values 1 and 2.
• 1 XOR 2 in binary is 01 XOR 10, resulting in 11, which is 3 in decimal.

We can pass the bit_xor() function to the bin() function to get the binary representation:

SELECT 
    bit_xor(value) AS decimal_representation,
    bin(bit_xor(value)) AS binary_representation
FROM (VALUES (1), (2)) AS t(value);

Output:

+------------------------+-----------------------+
| decimal_representation | binary_representation |
+------------------------+-----------------------+
| 3                      | 11                    |
+------------------------+-----------------------+

Grouped Data

We can use the GROUP BY clause to get the bitwise XOR against all values within a group.

Suppose we create the following table and insert data:

CREATE TABLE numbers (group_id INT, value INT);

INSERT INTO numbers (group_id, value)
VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 4),
(2, 5),
(3, 6),
(3, 7),
(3, 8),
(4, 9),
(4, 10),
(4, 11);

Now let’s use bit_xor() to return the bitwise XOR of the values in each group:

SELECT 
    group_id, 
    bit_xor(value) AS xor_result
FROM numbers
GROUP BY group_id;

Result:

+----------+------------+
| group_id | xor_result |
+----------+------------+
| 1        | 0          |
| 2        | 1          |
| 3        | 9          |
| 4        | 8          |
+----------+------------+

Here it is with an added column for the bin() function:

SELECT 
    group_id, 
    bit_xor(value) AS xor_result, 
    bin(bit_xor(value)) AS bin_result
FROM numbers
GROUP BY group_id;

Result:

+----------+------------+------------+
| group_id | xor_result | bin_result |
+----------+------------+------------+
| 1        | 0          | 0          |
| 2        | 1          | 1          |
| 3        | 9          | 1001       |
| 4        | 8          | 1000       |
+----------+------------+------------+