Every aggregate so far has worked on a value's magnitude, its sum, its average, its spread. A bitwise aggregate instead works on a value's individual bits, combining a group of integers bit position by bit position, useful for flag-style columns where each bit means something separate, like a set of permission flags packed into one integer per row.
Learning Objectives
- Define BIT_AND, BIT_OR, and BIT_XOR, and what each one computes bit-by-bit across a group.
- Know genuinely which of these six databases support all three, which support only two, and which support none.
- Explain PostgreSQL's real, specific version gap for BIT_XOR versus its longer-standing BIT_AND and BIT_OR.
Group of Integers
Each one a set of bit flags
Combine Bit-by-Bit
AND, OR, or XOR across every value
One Combined Integer
Same width as the inputs
Support, by Database
| Database | BIT_AND | BIT_OR | BIT_XOR |
|---|---|---|---|
| MySQL / MariaDB | Yes | Yes | Yes |
| PostgreSQL | Yes | Yes | Only in PostgreSQL 14+, a genuinely later addition than the other two |
| SQL Server / Oracle / SQLite | No built-in function | No built-in function | No built-in function |
Half of these six databases, SQL Server, Oracle, and SQLite, have no bitwise aggregate function at all, any equivalent there needs a manual, custom implementation outside plain SQL.
Using Them
BIT_OR finds every bit that's set in at least one row of the group, BIT_AND finds only the bits set in every single row, BIT_XOR flips a bit on if it's set in an odd number of rows, off if set in an even number.
SELECT department_id,
BIT_OR(permission_flags) AS any_permission_set,
BIT_AND(permission_flags) AS permissions_everyone_shares
FROM employee_permissions
GROUP BY department_id;Try It Yourself
Find which permission bits are set in every employee's flags within department id 3, combined, on PostgreSQL.
SELECT BIT_AND(permission_flags) AS permissions_everyone_shares FROM employee_permissions WHERE department_id = 3;
Frequently Asked Questions
What's a realistic use case for BIT_XOR specifically, versus BIT_AND or BIT_OR?
BIT_XOR is less commonly reached for directly in everyday queries, it's more often used as a building block for change-detection or parity-style checks across a group, where whether an even or odd number of rows had a bit set is itself the interesting signal.
Is there any portable way to get these on SQL Server, Oracle, or SQLite?
Not as a single built-in aggregate function, the honest answer is a custom aggregate (SQLite's extension API, a SQL Server CLR aggregate, or a PL/SQL-based approach on Oracle), genuinely outside plain SQL, or restructuring the problem to avoid needing a bitwise aggregate at all.
Common Beginner Mistakes
Assuming BIT_XOR exists wherever BIT_AND and BIT_OR do, on PostgreSQL in particular
It doesn't on PostgreSQL before version 14, BIT_AND and BIT_OR have been there far longer, BIT_XOR is a genuinely more recent addition, worth checking the PostgreSQL version actually in use before relying on it.
Expecting any built-in bitwise aggregate on SQL Server, Oracle, or SQLite
None of the three has one at all, a custom aggregate, or a different query structure entirely, is the real, honest answer there, not a differently-named built-in function waiting to be found.
Best Practices
- Check the PostgreSQL version specifically before relying on BIT_XOR, 14+ is required even though BIT_AND/BIT_OR work on much older versions.
- Reach for a bitwise aggregate only when the column is genuinely a packed set of bit flags, not as a stand-in for a properly normalized set of boolean or lookup columns.
- On the three databases with no built-in support, consider whether the flags column should be normalized into separate rows instead, before building a custom aggregate just to work around the gap.
Interview Questions
What does BIT_OR compute across a group, and give a realistic column it would be used on?
BIT_OR finds every bit position that's set to 1 in at least one row of the group, combining them into a single integer representing the union of all the flags seen. A realistic use is a packed permission-flags integer column, finding whether any employee in a department has a specific permission bit set at all.
Which of these six databases have no bitwise aggregate functions at all, and what's the honest workaround?
SQL Server, Oracle, and SQLite, none of the three. The honest workaround is a custom aggregate function through whichever extension mechanism that database offers (SQLite's C extension API, a SQL Server CLR aggregate, a PL/SQL-based approach on Oracle), or restructuring the schema to avoid needing one, there's no built-in equivalent waiting under a different name.
Summary
BIT_AND, BIT_OR, and BIT_XOR combine a group's integers bit-by-bit. MySQL and MariaDB have all three, PostgreSQL has BIT_AND and BIT_OR long-standing but BIT_XOR only since version 14, and SQL Server, Oracle, and SQLite have none built in at all, needing a custom aggregate instead.
What's Next?
Boolean aggregates, BOOL_AND, BOOL_OR, and the standard's EVERY, PostgreSQL's native way to aggregate true/false values directly, and the manual MIN/MAX-based workaround everywhere else.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →