Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT 6 & 3, 6 | 3, ~6, 1 << 4, 256 >> 2;

Try it

SQL The five operators

Press Run (or Ctrl + Enter) to see the result here.

SQL Permission flags in one integer column

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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) <> 0 rather than relying on it.
  • Operands must be integers. SQLite converts reals to integers first; PostgreSQL rejects numeric; MySQL converts. Cast explicitly.
  • SQLite shifts: 1 << 64 gives 0, and >> is an arithmetic shift, so negative values stay negative (-8 >> 1 is -4).
  • No index help. A WHERE (flags & 4) <> 0 filter 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.