MySQL Tutorial - UNION & UNION ALL

UNION and UNION ALL are used to combine the results of two or more SELECT statements into a single result set. 

The main difference between the two is that UNION eliminates duplicates in the result set, whereas UNION ALL does not.

What is in our table:

mysql> SELECT * FROM Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
|    5 |         6584568 | NULL |
|    6 |         9988559 | NULL |
+------+-----------------+------+
6 rows in set (0.00 sec)

mysql>

Table structure:

mysql> DESCRIBE Documents;
+-----------------+------+------+-----+---------+----------------+
| Field           | Type | Null | Key | Default | Extra          |
+-----------------+------+------+-----+---------+----------------+
| d_id            | int  | NO   | PRI | NULL    | auto_increment |
| internal_number | int  | YES  |     | NULL    |                |
| s_id            | int  | YES  |     | NULL    |                |
+-----------------+------+------+-----+---------+----------------+
3 rows in set (0.05 sec)

mysql>

We will create one table:

mysql> CREATE TABLE internalcontrol (
    -> d_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> internal_number INT,
    -> s_id INT);
Query OK, 0 rows affected (1.19 sec)

mysql>

The d_id column is defined as an integer and set to be the primary key for the table using the NOT NULL, PRIMARY KEY, and AUTO_INCREMENT keywords. 

This means that every row in the table will have a unique d_id value, and the value for d_id will be automatically incremented for each new row inserted into the table.

The internal_number and s_id columns are also defined as integers, but no additional constraints or settings are specified. These columns will be used to store data related to the internal control documents and the stores that they are associated with. 

mysql> INSERT INTO internalcontrol
    -> VALUES (NULL, 1235547, 1);
Query OK, 1 row affected (0.13 sec)

mysql> INSERT INTO internalcontrol
    -> VALUES (NULL, 6665555, 7);
Query OK, 1 row affected (0.12 sec)

mysql>

These two MySQL commands are inserting data into the internalcontrol table:

Content of our table now: 

mysql> SELECT * FROM internalcontrol;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         6665555 |    7 |
+------+-----------------+------+
2 rows in set (0.00 sec)

mysql>

UNION example:

mysql> SELECT * FROM internalcontrol
    -> UNION
    -> SELECT * FROM Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         6665555 |    7 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
|    5 |         6584568 | NULL |
|    6 |         9988559 | NULL |
+------+-----------------+------+
7 rows in set (0.02 sec)

mysql>

The resulting output shows all of the columns from both tables concatenated together into a single result set, with the d_id column from internalcontrol being used as the primary key column for the combined result set. 

UNION ALL Example:

mysql> SELECT * FROM internalcontrol
    -> UNION ALL
    -> SELECT * FROM Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         6665555 |    7 |
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
|    5 |         6584568 | NULL |
|    6 |         9988559 | NULL |
+------+-----------------+------+
8 rows in set (0.00 sec)

mysql>

UNION ALL returns all rows from both tables, even if there are duplicate rows.

Note that in this case, the two tables being combined have the same column names and data types, but this is not required for using UNION ALL. 

In the results of the UNION ALL query, we see NULL in the s_id column for rows with an internal_number value that does not have a matching s_id value in the internalcontrol table. 

This is because the Documents table does not have a corresponding s_id value for those rows, so the value is NULL.

mysql> SELECT * FROM Documents
    -> UNION ALL
    -> SELECT * FROM internalcontrol;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
|    5 |         6584568 | NULL |
|    6 |         9988559 | NULL |
|    1 |         1235547 |    1 |
|    2 |         6665555 |    7 |
+------+-----------------+------+
8 rows in set (0.00 sec)

mysql>

This code is performing a UNION ALL operation between the Documents table and the internalcontrol table. The resulting table contains all the rows from both tables, with duplicates included.

In this case, the resulting table has 8 rows, with the same rows as the previous example. The only difference is the order in which the rows appear, since UNION ALL does not sort the results.