Creating Unions That Join
mysql> mysql> CREATE TABLE Books -> ( -> BookID SMALLINT NOT NULL PRIMARY KEY, -> BookTitle VARCHAR(60) NOT NULL, -> Copyright YEAR NOT NULL -> ) -> ENGINE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO Books VALUES -> (12786, 'Notebook', 1934), -> (13331, 'C++', 1919), -> (14356, 'Opera', 1966), -> (15729, 'Sql Server', 1932), -> (16284, 'C', 1996), -> (17695, 'Pascal', 1980), -> (19264, 'Postcards', 1992), -> (19354, 'Oracle', 1993); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> CREATE TABLE Authors -> ( -> AuthID SMALLINT NOT NULL PRIMARY KEY, -> AuthFN VARCHAR(20), -> AuthMN VARCHAR(20), -> AuthLN VARCHAR(20) -> ) -> ENGINE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO Authors VALUES -> (1006, 'Hunter', 'S.', 'Thompson'), -> (1007, 'Joyce', 'Carol', 'Oates'), -> (1008, 'Black', NULL, 'Elk'), -> (1009, 'Rainer', 'Maria', 'Rilke'), -> (1010, 'John', 'Kennedy', 'Toole'), -> (1011, 'John', 'G.', 'Neihardt'), -> (1012, 'Annie', NULL, 'Proulx'), -> (1013, 'Alan', NULL, 'Watts'), -> (1014, 'Nelson', NULL, 'Algren'); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> CREATE TABLE AuthorBook -> ( -> BookID SMALLINT NOT NULL, -> AuthID SMALLINT NOT NULL, -> PRIMARY KEY (AuthID, BookID) -> ) -> ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO AuthorBook VALUES -> (1006, 14356), -> (1008, 15729), -> (1009, 12786), -> (1010, 17695), -> (1011, 15729), -> (1012, 19264), -> (1012, 19354), -> (1014, 16284); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> mysql> CREATE TABLE Authors2 -> ( -> AuthID SMALLINT NOT NULL PRIMARY KEY, -> AuthFN VARCHAR(20), -> AuthMN VARCHAR(20), -> AuthLN VARCHAR(20) -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO Authors2 VALUES -> (1006, 'Mark', NULL, 'Twain'), -> (2205, 'E.', 'M.', 'Forster'), -> (2206, 'Gabriel', 'Garcia', 'Marquez'), -> (2207, 'Raymond', NULL, 'Carver'), -> (2208, 'Mary', NULL, 'Shelley'), -> (2209, 'Albert', NULL, 'Camus'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT AuthFN, AuthMN, AuthLN FROM Authors -> UNION -> SELECT AuthFN, AuthMN, AuthLN FROM Authors2; +---------+---------+----------+ | AuthFN | AuthMN | AuthLN | +---------+---------+----------+ | Hunter | S. | Thompson | | Joyce | Carol | Oates | | Black | NULL | Elk | | Rainer | Maria | Rilke | | John | Kennedy | Toole | | John | G. | Neihardt | | Annie | NULL | Proulx | | Alan | NULL | Watts | | Nelson | NULL | Algren | | Mark | NULL | Twain | | E. | M. | Forster | | Gabriel | Garcia | Marquez | | Raymond | NULL | Carver | | Mary | NULL | Shelley | | Albert | NULL | Camus | +---------+---------+----------+ 15 rows in set (0.00 sec) mysql> mysql> mysql> drop table Books; Query OK, 0 rows affected (0.00 sec) mysql> drop table Authors; Query OK, 0 rows affected (0.00 sec) mysql> drop table Authors2; Query OK, 0 rows affected (0.00 sec) mysql> drop table AuthorBook; Query OK, 0 rows affected (0.00 sec) mysql>