w3resource

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:

SELECT 1 XOR 1;

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:

SELECT 1 XOR 0;

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:

SELECT 1 XOR 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:

SELECT 1 XOR 1 XOR 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



Follow us on Facebook and Twitter for latest update.