Where clause: XOR : Where « Where Clause « SQL / MySQL

1. SQL / MySQL
2. Where Clause
3. Where

Where clause: XOR

   
/*
mysql> Drop table Books;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE Books
    -> (
    ->    ID SMALLINT NOT NULL PRIMARY KEY,
    ->    Name VARCHAR(40) NOT NULL,
    ->    Category VARCHAR(15),
    ->    InStock SMALLINT NOT NULL,
    ->    OnOrder SMALLINT NOT NULL
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO Books
    -> VALUES (101, 'On', 'Nonfiction', 13, 11),
    ->        (102, 'News', 'Fiction', 15, 21),
    ->        (103, 'Hello', 'Nonfiction', 21, 32),
    ->        (104, 'Poet', 'Nonfiction', 35, 13),
    ->        (105, 'Dunces', 'Fiction', 5, 35),
    ->        (106, 'One', 'Fiction', 28, 14),
    ->        (107, 'From', NULL, 46, 31);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from Books;
+-----+--------+------------+---------+---------+
| ID  | Name   | Category   | InStock | OnOrder |
+-----+--------+------------+---------+---------+
| 101 | On     | Nonfiction |      13 |      11 |
| 102 | News   | Fiction    |      15 |      21 |
| 103 | Hello  | Nonfiction |      21 |      32 |
| 104 | Poet   | Nonfiction |      35 |      13 |
| 105 | Dunces | Fiction    |       5 |      35 |
| 106 | One    | Fiction    |      28 |      14 |
| 107 | From   | NULL       |      46 |      31 |
+-----+--------+------------+---------+---------+
7 rows in set (0.00 sec)

mysql> SELECT Name, Category, InStock, OnOrder
    -> FROM Books
    -> WHERE Category='Fiction' XOR InStock IS NULL
    -> ORDER BY Name;
+--------+----------+---------+---------+
| Name   | Category | InStock | OnOrder |
+--------+----------+---------+---------+
| Dunces | Fiction  |       5 |      35 |
| News   | Fiction  |      15 |      21 |
| One    | Fiction  |      28 |      14 |
+--------+----------+---------+---------+
3 rows in set (0.00 sec)


*/
Drop table Books;

CREATE TABLE Books
(
   ID SMALLINT NOT NULL PRIMARY KEY,
   Name VARCHAR(40) NOT NULL,
   Category VARCHAR(15),
   InStock SMALLINT NOT NULL,
   OnOrder SMALLINT NOT NULL
);


INSERT INTO Books
VALUES (101, 'On', 'Nonfiction', 13, 11),
       (102, 'News', 'Fiction', 15, 21),
       (103, 'Hello', 'Nonfiction', 21, 32),
       (104, 'Poet', 'Nonfiction', 35, 13),
       (105, 'Dunces', 'Fiction', 5, 35),
       (106, 'One', 'Fiction', 28, 14),
       (107, 'From', NULL, 46, 31);


select * from Books;

SELECT Name, Category, InStock, OnOrder
FROM Books
WHERE Category='Fiction' XOR InStock IS NULL
ORDER BY Name;


           
         
    
    
  

Related examples in the same category