MySQL Tutorial/Logic Operator/XOR

Logical XOR (XOR)

"a XOR b" is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).

Returns NULL if either operand is NULL.

For non-NULL operands, evaluates to 1 if an odd number of operands is non-zero, otherwise 0 is returned.

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

Using Logical XOR operator with NULL value

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

Using XOR to link two conditions

mysql>
mysql>
mysql> CREATE TABLE Books(
    ->    BookID SMALLINT NOT NULL PRIMARY KEY,
    ->    BookName VARCHAR(40) NOT NULL,
    ->    Category VARCHAR(15),
    ->    InStock SMALLINT NOT NULL,
    ->    OnOrder SMALLINT NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT INTO Books VALUES (101, "Java",           "Nonfiction", 12, 13),
    ->                          (102, "MySQL",          "Fiction",    17, 20),
    ->                          (103, "Oracle",         "Nonfiction", 23, 33),
    ->                          (104, "VB.net",         "Nonfiction", 32, 12),
    ->                          (105, "www.sqle.ru", "Fiction",    6,  35),
    ->                          (106, "Perl",           "Fiction",    28, 14),
    ->                          (107, "Php",             NULL,        46, 3);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql>
mysql>
mysql> select * from Books;
+--------+----------------+------------+---------+---------+
| BookID | BookName       | Category   | InStock | OnOrder |
+--------+----------------+------------+---------+---------+
|    101 | Java           | Nonfiction |      12 |      13 |
|    102 | MySQL          | Fiction    |      17 |      20 |
|    103 | Oracle         | Nonfiction |      23 |      33 |
|    104 | VB.net         | Nonfiction |      32 |      12 |
|    105 | www.sqle.ru | Fiction    |       6 |      35 |
|    106 | Perl           | Fiction    |      28 |      14 |
|    107 | Php            | NULL       |      46 |       3 |
+--------+----------------+------------+---------+---------+
7 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT BookName, Category, InStock, OnOrder
    -> FROM Books
    -> WHERE Category="Fiction" XOR InStock IS NULL
    -> ORDER BY BookName;
+----------------+----------+---------+---------+
| BookName       | Category | InStock | OnOrder |
+----------------+----------+---------+---------+
| MySQL          | Fiction  |      17 |      20 |
| Perl           | Fiction  |      28 |      14 |
| www.sqle.ru | Fiction  |       6 |      35 |
+----------------+----------+---------+---------+
3 rows in set (0.00 sec)
mysql> drop table Books;
Query OK, 0 rows affected (0.00 sec)
mysql>