MySQL XOR operator

XOR operator

MySQL XOR operator checks two operands (or expressions) and returns TRUE if one or the other but not both is TRUE.

This function is useful in -

• The term "XOR" stands for Exclusive OR, which means that it returns true only when one of the conditions is true, but not both.
• XOR is commonly used when you have two mutually exclusive options. Suppose, in a registration form, a user have to provide either an email or a phone number, but not both, XOR is ideal for enforcing such constraints.
• By using XOR, you can enforce that certain combinations of data are mutually exclusive.
• XOR provides a way to logically combine conditions, allowing you to express complex logic in your queries.
• XOR makes your query more explicit. It clearly states that only one of the conditions must be true, which can help prevent misunderstandings or misinterpretations of your query.
• XOR can be used in UPDATE statements to conditionally change a field based on its current value.
• When building complex conditional logic, XOR provides a way to express that only one of the conditions must be true.

Syntax:

XOR

MySQL Logical XOR returns a NULL when one of the operands is NULL. It returns 1 when one operand is NULL and an odd number of operands are nonzero and returns 0 when no operand is NULL and even number of operands are nonzero. The actual mathematical representation of a XOR equation "A XOR B" is "(A AND (NOT B)) OR ((NOT A) AND B)".

MySQL Version: 8.0

Example: MySQL XOR operator

In the following MySQL statement, both of the operands are true, so it returns FALSE.

Code:

-- This SQL statement performs a logical XOR operation between the numbers 1 and 1
-- Explanation: The query evaluates a logical XOR operation between the numbers 1 and 1, returning the result of the operation.
SELECT 1 XOR 1;  -- The SELECT statement is used to evaluate the expression '1 XOR 1' and return the result

Explanation:

• The SELECT statement is used to evaluate expressions and return the result.


• The XOR operator is a logical XOR (exclusive OR) operator in MySQL.


• In the context of the expression 1 XOR 1:


• XOR returns TRUE if one of the operands is TRUE, but not both.


• Both operands (1 and 1) are considered as TRUE.


• Since both operands are the same, the XOR operation returns FALSE.


• TRUE XOR TRUE evaluates to FALSE, which is represented as 0 in MySQL.


• Therefore, the result of SELECT 1 XOR 1 is 0.

Output:

MySQL> SELECT 1 XOR 1;
+---------+
| 1 XOR 1 |
+---------+
|       0 | 
+---------+
1 row in set (0.00 sec)

Example of MySQL XOR operator with at least one true operand

In the above MySQL statement, one of the operands is true, so it returns TRUE.

Code:

-- This SQL statement performs a logical XOR operation between the numbers 1 and 0
-- Explanation: The query evaluates a logical XOR operation between the numbers 1 and 0, returning the result of the operation.
SELECT 1 XOR 0;  -- The SELECT statement is used to evaluate the expression '1 XOR 0' and return the result

Explanation:

• The SELECT statement is used to evaluate expressions and return the result.


• The XOR operator is a logical XOR (exclusive OR) operator in MySQL.


• In the context of the expression 1 XOR 0:


• XOR returns TRUE if one of the operands is TRUE, but not both.


• 1 is considered as TRUE and 0 is considered as FALSE.


• Since one operand (1) is TRUE and the other operand (0) is FALSE, the XOR operation returns TRUE.


• TRUE XOR FALSE evaluates to TRUE, which is represented as 1 in MySQL.


• Therefore, the result of SELECT 1 XOR 0 is 1.

Output:

MySQL> SELECT 1 XOR 0;
+---------+
| 1 XOR 0 |
+---------+
|       1 | 
+---------+
1 row in set (0.00 sec)

Example of MySQL XOR operator with a NULL operand

In the following MySQL statement, one of the operands is NULL, so it returns NULL.

Code:

-- This SQL statement performs a logical XOR operation between the number 1 and NULL
-- Explanation: The query evaluates a logical XOR operation between the number 1 and NULL, returning the result of the operation.
SELECT 1 XOR NULL;  -- The SELECT statement is used to evaluate the expression '1 XOR NULL' and return the result

Explanation:

• The SELECT statement is used to evaluate expressions and return the result.


• The XOR operator is a logical XOR (exclusive OR) operator in MySQL.


• In the context of the expression 1 XOR NULL:


• NULL represents an unknown or undefined value in SQL.


• XOR returns TRUE if one of the operands is TRUE, but not both.


• Since one operand (1) is TRUE, but the other operand (NULL) is unknown, the result of the XOR operation is also NULL because the truth value is unknown.


• Therefore, the result of SELECT 1 XOR NULL is NULL.

Output:

MySQL> SELECT 1 XOR NULL;
+------------+
| 1 XOR NULL |
+------------+
|       NULL | 
+------------+
1 row in set (0.01 sec)

Example of more than one XOR operators

In the following MySQL statement, for 1 XOR 1, both of the operands are TRUE, so it returns FALSE; again, for the next XOR, one of the operands is TRUE, so it returns TRUE. So, the final output is TRUE.

Code:

-- This SQL statement performs multiple logical XOR operations between the number 1
-- Explanation: The query evaluates multiple logical XOR operations between the number 1, returning the result of the final operation.
SELECT 1 XOR 1 XOR 1;  -- The SELECT statement is used to evaluate the expression '1 XOR 1 XOR 1' and return the result

Explanation:

• The SELECT statement is used to evaluate expressions and return the result.


• The XOR operator is a logical XOR (exclusive OR) operator in MySQL.


• In the context of the expression 1 XOR 1 XOR 1:


• XOR returns TRUE if one of the operands is TRUE, but not both.


• Since all operands (1) are the same, only one TRUE is considered.


• The operation is left-associative, meaning it is evaluated from left to right.


• 1 XOR 1 evaluates to 0 because both operands are the same (TRUE XOR TRUE evaluates to FALSE).


• 0 XOR 1 evaluates to 1 because one operand is FALSE and the other is TRUE.


• Therefore, the result of SELECT 1 XOR 1 XOR 1 is 1.

Output:

MySQL> SELECT 1 XOR 1 XOR 1;
+---------------+
| 1 XOR 1 XOR 1 |
+---------------+
|             1 | 
+---------------+
1 row in set (0.00 sec)

Previous: OR operator
Next: MySQL Control-flow-functions CASE operator