BIT_OR and BIT_AND are aggregate functions that walk an integer column across a group and apply bitwise OR or AND to every value at once. They shine when permissions, features, or traits are packed as bits inside a single number: one query tells you which bits appear anywhere and which are set on every row.
What BIT_OR, BIT_AND and BIT_XOR compute
Picture a user's permissions packed into one number: bit 1 is read, bit 2 is write, bit 4 is delete. The three aggregates then answer different questions about a group of rows.
SELECT
user_id,
BIT_OR(flags) AS any_bit_set,
BIT_AND(flags) AS bits_in_every_row,
BIT_XOR(flags) AS parity
FROM permissions
GROUP BY user_id;
BIT_OR is a union: a bit is set if it appeared in at least one row. This builds a combined mask of all permissions.BIT_AND is an intersection: a bit survives only if it is present in every row of the group. It is the common denominator.BIT_XOR is parity: a bit is 1 if it occurred an odd number of times. Handy for checksums and spotting unpaired values.
The result is an ordinary integer, which you then read with the & operator.
Combining permission masks with BIT_OR
The most common case is collecting every permission a user holds from many rows (across roles, teams, or resources):
SELECT
user_id,
BIT_OR(flags) AS effective_mask
FROM permissions
GROUP BY user_id;
Once you have the mask, test a specific bit with the same bitwise AND. For example, who can delete (bit 4):
SELECT user_id
FROM permissions
GROUP BY user_id
HAVING (BIT_OR(flags) & 4) = 4;
Gotcha: write (mask & 4) = 4, not mask & 4 = 4. In PostgreSQL = binds tighter than &, so without parentheses the expression parses as mask & (4 = 4) and either fails on types or returns nonsense. Always parenthesize the bitwise operation.
The same trick scales to countries or departments, for instance which features are active for at least one user in a country:
SELECT
u.country,
BIT_OR(p.flags) AS country_features
FROM users u
JOIN permissions p ON p.user_id = u.id
GROUP BY u.country;
Finding common bits with BIT_AND
BIT_AND answers "what does everyone have." Say you need the permissions shared by every employee in a department, the guaranteed minimum access:
SELECT
e.dept,
BIT_AND(p.flags) AS common_to_all
FROM employees e
JOIN permissions p ON p.user_id = e.id
GROUP BY e.dept;
A result of 0 means there is no shared bit at all. This is great for invariant checks: for example, the "active" bit (value 1) should be set on every row for a user.
SELECT user_id
FROM permissions
GROUP BY user_id
HAVING (BIT_AND(flags) & 1) = 0;
Modeling flags: practical habits
To keep bits from becoming magic numbers, label them and compute the mask explicitly:
SELECT
user_id,
BIT_OR(flags) AS mask,
(BIT_OR(flags) & 1) <> 0 AS can_read,
(BIT_OR(flags) & 2) <> 0 AS can_write,
(BIT_OR(flags) & 4) <> 0 AS can_delete
FROM permissions
GROUP BY user_id;
A few modeling guidelines:
- One bit is one independent trait. Do not encode mutually exclusive states as bits; use a separate status column for that.
- A 32-bit
integer holds 31 safe flags (the top bit is the sign); use bigint for more.
- The aggregates ignore
NULL, just like SUM or COUNT. An empty group yields NULL.
- If flags live in a
bit(n) type, the same BIT_OR and BIT_AND work over it and return a bit string.
Gotcha: bitwise aggregates do not replace normalization. If you need "find everyone with permission X," an index on the packed number will not help, because the planner cannot search a single bit. For such queries, keep an expression index on ((flags & 4)) or move permissions into their own table.
MySQL and ClickHouse
MySQL also has BIT_OR, BIT_AND, and BIT_XOR with the same syntax, though a non-zero comparison reads more cleanly when testing a bit:
SELECT user_id
FROM permissions
GROUP BY user_id
HAVING (BIT_OR(flags) & 4) <> 0;
Differences worth remembering:
- In older MySQL,
BIT_AND over an empty group returned "all ones" (for example, 18446744073709551615) instead of NULL. Verify your version's behavior.
- In ClickHouse the functions are
groupBitOr, groupBitAnd, and groupBitXor: different names, identical meaning.
- Everywhere the result stays an integer, so read individual bits with
& and parentheses, mindful of operator precedence.
When state is packed into bits, these three aggregates give exact answers to "anyone / everyone / odd count" in a single pass over the group, with no unpacking into rows and no application-side logic.
BIT_ORandBIT_ANDare aggregate functions that walk an integer column across a group and apply bitwise OR or AND to every value at once. They shine when permissions, features, or traits are packed as bits inside a single number: one query tells you which bits appear anywhere and which are set on every row.What BIT_OR, BIT_AND and BIT_XOR compute
Picture a user's permissions packed into one number: bit 1 is read, bit 2 is write, bit 4 is delete. The three aggregates then answer different questions about a group of rows.
SELECT user_id, BIT_OR(flags) AS any_bit_set, BIT_AND(flags) AS bits_in_every_row, BIT_XOR(flags) AS parity FROM permissions GROUP BY user_id;BIT_ORis a union: a bit is set if it appeared in at least one row. This builds a combined mask of all permissions.BIT_ANDis an intersection: a bit survives only if it is present in every row of the group. It is the common denominator.BIT_XORis parity: a bit is 1 if it occurred an odd number of times. Handy for checksums and spotting unpaired values.The result is an ordinary integer, which you then read with the
&operator.Combining permission masks with BIT_OR
The most common case is collecting every permission a user holds from many rows (across roles, teams, or resources):
SELECT user_id, BIT_OR(flags) AS effective_mask FROM permissions GROUP BY user_id;Once you have the mask, test a specific bit with the same bitwise AND. For example, who can delete (bit 4):
SELECT user_id FROM permissions GROUP BY user_id HAVING (BIT_OR(flags) & 4) = 4;The same trick scales to countries or departments, for instance which features are active for at least one user in a country:
SELECT u.country, BIT_OR(p.flags) AS country_features FROM users u JOIN permissions p ON p.user_id = u.id GROUP BY u.country;Finding common bits with BIT_AND
BIT_ANDanswers "what does everyone have." Say you need the permissions shared by every employee in a department, the guaranteed minimum access:SELECT e.dept, BIT_AND(p.flags) AS common_to_all FROM employees e JOIN permissions p ON p.user_id = e.id GROUP BY e.dept;A result of 0 means there is no shared bit at all. This is great for invariant checks: for example, the "active" bit (value 1) should be set on every row for a user.
SELECT user_id FROM permissions GROUP BY user_id HAVING (BIT_AND(flags) & 1) = 0; -- somebody is missing the active bitModeling flags: practical habits
To keep bits from becoming magic numbers, label them and compute the mask explicitly:
-- bit layout: 1 = read, 2 = write, 4 = delete, 8 = export SELECT user_id, BIT_OR(flags) AS mask, (BIT_OR(flags) & 1) <> 0 AS can_read, (BIT_OR(flags) & 2) <> 0 AS can_write, (BIT_OR(flags) & 4) <> 0 AS can_delete FROM permissions GROUP BY user_id;A few modeling guidelines:
integerholds 31 safe flags (the top bit is the sign); usebigintfor more.NULL, just likeSUMorCOUNT. An empty group yieldsNULL.bit(n)type, the sameBIT_ORandBIT_ANDwork over it and return a bit string.MySQL and ClickHouse
MySQL also has
BIT_OR,BIT_AND, andBIT_XORwith the same syntax, though a non-zero comparison reads more cleanly when testing a bit:-- MySQL: users that can delete (bit 4) SELECT user_id FROM permissions GROUP BY user_id HAVING (BIT_OR(flags) & 4) <> 0;Differences worth remembering:
BIT_ANDover an empty group returned "all ones" (for example, 18446744073709551615) instead ofNULL. Verify your version's behavior.groupBitOr,groupBitAnd, andgroupBitXor: different names, identical meaning.&and parentheses, mindful of operator precedence.When state is packed into bits, these three aggregates give exact answers to "anyone / everyone / odd count" in a single pass over the group, with no unpacking into rows and no application-side logic.