SQL XOR

What does the SQL XOR operator do?

XOR returns true when exactly one of the two compared Boolean expressions is true.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL XOR:

SQL XOR Full Explanation

XOR is a logical operator that implements exclusive OR semantics. It evaluates two Boolean expressions and returns 1 (true) if either expression is true but not both; otherwise it returns 0 (false). XOR is not part of the ANSI SQL standard, but MySQL and MariaDB include it as both a logical and bitwise operator. When used in a Boolean context, non-zero numeric values are treated as true and zero as false. When either operand is NULL, the result is NULL, mirroring three-valued logic behavior. Databases that lack a native XOR operator can emulate it with expressions such as `(a AND NOT b) OR (NOT a AND b)` or with `a <> b` for Boolean inputs. XOR is evaluated after OR but before the assignment operator in MySQL’s operator precedence table. Because of its limited dialect support, portable code should prefer an emulation expression instead of the native keyword.

SQL XOR Syntax

expression1 XOR expression2

SQL XOR Parameters

expression1 (Boolean or numeric) - First value to compare
expression2 (Boolean or numeric) - Second value to compare

Example Queries Using SQL XOR

-- Simple truth table
SELECT 0 XOR 0 AS result_false,       -- returns 0
       0 XOR 1 AS result_true_1,      -- returns 1
       1 XOR 0 AS result_true_2,      -- returns 1
       1 XOR 1 AS result_false_2;     -- returns 0

-- Using columns in a WHERE clause
SELECT user_id
FROM   users
WHERE  is_active XOR is_admin; -- finds rows where exactly one flag is set

-- Emulating XOR in PostgreSQL
SELECT user_id
FROM   users
WHERE  (is_active AND NOT is_admin) OR (NOT is_active AND is_admin);

Expected Output Using SQL XOR

Each SELECT returns 1 when exactly one operand is true, 0 when both are true or both are false, and NULL if any operand is NULL
Queries filter or compute rows accordingly

Use Cases with SQL XOR

Filter rows where two status flags are mutually exclusive
Build conditional aggregates that count records meeting exactly one criterion
Simplify readability when exclusive logic is clearer than compound AND/OR expressions
Implement feature toggles where only one of two settings may be enabled

Common Mistakes with SQL XOR

Assuming XOR is portable across databases - only MySQL and MariaDB support it natively
Forgetting that non-zero values evaluate to true, which can lead to unexpected results with numeric data
Overlooking NULL propagation - if either operand is NULL, the result is NULL, not 0 or 1
Misusing XOR for bitwise operations on integers larger than 1 bit without casting to Boolean

First Introduced In

MySQL 3.x

Frequently Asked Questions

What databases have a built-in XOR operator?

Only MySQL and MariaDB implement XOR natively. Other systems require an emulation expression.

How can I write a portable XOR comparison?

Use `(expr1 AND NOT expr2) OR (NOT expr1 AND expr2)` or, for Boolean columns, `expr1 <> expr2`.

Is XOR the same as bitwise XOR ( ^ )?

No. MySQL treats XOR as a logical operator on Boolean values, while `^` is a bitwise XOR on integer bits.

What is the precedence of XOR in MySQL?

XOR has lower precedence than AND but higher precedence than assignment operators. Use parentheses to guarantee evaluation order when mixing operators.