SQL UNION operator

The SQL UNION operator is used to combine multiple SELECT statements into one result set. The syntax:

SELECT column_name FROM table_1 UNION SELECT column_name FROM table_2;

In the next example we will use two tables: the Customers table and the Suppliers table.

Customers Table:

	
+----+------------+-------------------+-----------+-------+-------+
| id | name       | address           | city      | state | zip   |
+----+------------+-------------------+-----------+-------+-------+
|  1 | Bill Smith | 123 Main Street   | Hope      | CA    | 98765 |
|  2 | Mary Smith | 123 Dorian Street | Harmony   | AZ    | 98765 |
|  3 | Bob Smith  | 123 Laugh Street  | Hope      | CA    | 98765 |
|  4 | Chang Chao | 123 Dorian Streat | Hong Kong | CN    | 98765 |
|  5 | John Smith | 123 Winges road   | Toronto   | CA    | 98765 |
|  6 | John Doe   | 1234 North road   | Toronto   | CA    | 98765 |
+----+------------+-------------------+-----------+-------+-------+

 Suppliers table:

+----+------------+-------------+-----------------+--------------------+
| id | name       | address     | city            | state     | zip    |
+----+------------+-------------+-----------------+--------------------+
| 186 | Karttunen | Matti       | Helsinki        | Finland   |   1501 |
| 187 | Ashworth  | Rachel      | Manchester      | UK        |   1501 |
| 189 | Cassidy   | Dean        | Dublin          | Ireland   |   3400 |
| 198 | Taylor    | Leslie      | Brickhaven      | USA       |   1216 |
| 201 | Devon     | Elizabeth   | Liverpool       | UK        |   1501 |
| 202 | Tamuri    | Yoshi       | Vancouver       | Canada    |   3300 |
| 204 | Barajas   | Miguel      | Brickhaven      | USA       |   1188 |
| 205 | Young     | Julie       | Pasadena        | USA       |   1166 |
| 206 | Walker    | Brydey      | Singapore       | Singapore |   5200 |
+-----+-----------+-------------+-----------------+-----------+--------+

To combine these two tables, we can use the following command:

SELECT city FROM customers UNION SELECT city FROM suppliers ORDER BY city;

This would produce the following result:

+-----------------+
|  city           |
+-----------------+
| Hope            | 
| Harmony         | 
| Hope            | 
| Hong Kong       | 
| Toronto         | 
| Toronto         | 
| Helsinki        | 
| Manchester      | 
| Dublin          | 
| Brickhaven      | 
| Liverpool       | 
| Vancouver       | 
| Brickhaven      | 
| Pasadena        | 
| Singapore       |
+-----------------+