Mega Code Archive
To select all records, including duplicates, follow the first UNION keyword with ALL
<![CDATA[
mysql>
mysql> CREATE TABLE prospect
-> (
-> fname CHAR(40),
-> lname CHAR(40),
-> addr CHAR(40)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE customer
-> (
-> last_name CHAR(40),
-> first_name CHAR(40),
-> address CHAR(40)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE vendor
-> (
-> company CHAR(60),
-> street CHAR(30)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO prospect (fname, lname, addr)
-> VALUES
-> ('Peter','Jones','482 Main St., Apt. 402'),
-> ('Bernice','Smith','916 Maple Dr.')
-> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO customer (first_name, last_name, address)
-> VALUES
-> ('Grace','Peterson','16055 First Ave.'),
-> ('Bernice','Smith','916 Maple Dr.'),
-> ('Walter','Brown','8602 1st St.')
-> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO vendor (company, street)
-> VALUES
-> ('Database, Inc.','38 Third Ave.'),
-> ('Xml, Ltd.','213B Commerce Park.')
-> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> SELECT fname, lname, addr FROM prospect
-> UNION ALL
-> SELECT first_name, last_name, address FROM customer
-> UNION
-> SELECT company, '', street FROM vendor;
+----------------+----------+------------------------+
| fname | lname | addr |
+----------------+----------+------------------------+
| Peter | Jones | 482 Main St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr. |
| Grace | Peterson | 16055 First Ave. |
| Walter | Brown | 8602 1st St. |
| Database, Inc. | | 38 Third Ave. |
| Xml, Ltd. | | 213B Commerce Park. |
+----------------+----------+------------------------+
6 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table prospect;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table customer;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table vendor;
Query OK, 0 rows affected (0.00 sec)
mysql>
]]>