SQL Reference ยท Operators and Predicates
SQL bitwise operators
Reference for SQL bitwise operators &, |, ~, << and >>: flags stored in integer columns, masks, shifts, NULL handling and dialect differences.
Bitwise operators work on the individual bits of integers. They let you pack several yes/no flags into one integer column (permissions, feature switches, status bits) and test or change them with a mask. & is bitwise AND, | is OR, ~ is NOT (flips every bit), << shifts bits left and >> shifts them right.
Bitwise operators are not in the core SQL standard, and their spelling differs between databases, especially XOR. Flag columns are compact but hard to read and cannot be indexed well, so use them sparingly.
SELECT 6 & 3, 6 | 3, ~6, 1 << 4, 256 >> 2;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
a & b -- AND a | b -- OR ~a -- NOT a << n -- shift left by n bits a >> n -- shift right by n bits
| Operator | Result | Example |
|---|---|---|
& |
A bit is 1 where both operands have 1. | 6 & 3 is 2 (110 and 011 give 010). |
| |
A bit is 1 where either operand has 1. | 6 | 3 is 7. |
~ |
Every bit flipped. For signed integers, ~n equals -n - 1. |
~6 is -7. |
<< |
Shift left, same as multiplying by 2 for each place (until overflow). | 1 << 4 is 16. |
>> |
Shift right, same as dividing by 2 and rounding down. | 256 >> 2 is 64. |
Returns: an integer. If any operand is NULL, the result is NULL.
Dialect differences
| Operation | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| AND, OR | &, | |
&, | |
&, | |
&, | |
BITAND(a, b) only |
| NOT | ~ |
~ |
~ |
~ |
No operator: -a - 1 |
| XOR | No operator: (a | b) - (a & b) |
# |
^ |
^ |
a + b - 2 * BITAND(a, b) |
| Shifts | <<, >> |
<<, >> |
<<, >> |
<<, >> (SQL Server 2022+) |
No operators |
| Result type | 64-bit signed | Same as operand | Unsigned 64-bit | Same as operand | Number |
-- PostgreSQL: XOR SELECT 6 # 3; -- 5 -- MySQL and SQL Server: XOR SELECT 6 ^ 3; -- 5 -- Oracle: AND SELECT BITAND(6, 3) FROM dual; -- 2
Usage notes
- Test a flag with a mask:
(permissions & 4) <> 0. Test for several at once with(permissions & 6) = 6. - Set a flag:
permissions | 2. Clear a flag:permissions & ~2. Toggle: XOR with the mask. - Use parentheses. Precedence of bitwise operators relative to arithmetic and comparison operators is not the same in every database, so write
(x & 4) <> 0rather than relying on it. - Operands must be integers. SQLite converts reals to integers first; PostgreSQL rejects
numeric; MySQL converts. Cast explicitly. - SQLite shifts:
1 << 64gives0, and>>is an arithmetic shift, so negative values stay negative (-8 >> 1is-4). - No index help. A
WHERE (flags & 4) <> 0filter scans the table. If you query flags often, store them as separate boolean columns or add a computed column with an index. - Design trade-off: separate columns or a junction table are easier to query, constrain and extend than packed bits. See normalization.