MySQLUNION Operator
The MySQL UNION Operator
TheUNION operator is used to combine the result-set of two or moreSELECT statements.
TheUNION operator automatically removes duplicate rows from the result set.
Requirements forUNION:
- Every
SELECTstatement withinUNIONmust have the same number of columns - The columns must also have similar data types
- The columns in every
SELECTstatement must also be in the same order
UNION Syntax
UNION
SELECTcolumn_name(s) FROMtable2;
Note: The column names in the result-set are usually equal to the column names in the firstSELECT statement.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
And a selection from the "Suppliers" table:
| SupplierID | SupplierName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Exotic Liquid | Charlotte Cooper | 49 Gilbert St. | London | EC1 4SD | UK |
| 2 | New Orleans Cajun Delights | Shelley Burke | P.O. Box 78934 | New Orleans | 70117 | USA |
| 3 | Grandma Kelly's Homestead | Regina Murphy | 707 Oxford Rd. | Ann Arbor | 48104 | USA |
SQL UNION Example
The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:
Note: If some customers or suppliers have the same city, each city will only be listed once, becauseUNION selects only distinct values. UseUNION ALL to also select duplicate values!
SQL UNION With WHERE
The following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:
Example
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
Another UNION Example
The following SQL statement lists all customers and suppliers:
Example
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;
Notice the "AS Type" above - it is an alias.SQL Aliases are used to give a table or a column a temporary name.An alias only exists for the duration of the query. So, here we have created a temporary column named "Type", that list whether the contact person is a "Customer" or a "Supplier".
