UNION

MySQL

Example

SELECT country FROM Suppliers
UNION
SELECT country FROM Customers;

Output

+-----------+
| country   |
+-----------+
| Germany   |
| USA       |
| Japan     |
| France    |
| UK        |
| Australia |
+-----------+

Explanation

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns, the columns must also have similar data types, and the columns in each SELECT statement must also be in the same order. In this example, we’re selecting country from two different tables: Suppliers and Customers. The resulting output is a list of countries from both tables, without any duplicate entries.

PostgreSQL

Example

SELECT 'John Doe' AS Employee_Name,
        'Development' AS Department
UNION
SELECT 'Jane Smith',
        'Marketing';

Output

 Employee_Name |   Department
---------------+---------------
 John Doe      | Development
 Jane Smith    | Marketing

Explanation

The UNION operator is used to combine rows from two or more SELECT statements. In the given example, two SELECT statements are combined to create one single set of results. The first SELECT statement yields ‘John Doe’ as the Employee Name and ‘Development’ as the Department. The second statement yields ‘Jane Smith’ as the Employee Name and ‘Marketing’ as the Department. The UNION between them outputs two rows, one for each SELECT statement.

SQL Server

Example

SELECT City, Country FROM Customers
UNION
SELECT City, Country FROM Suppliers
ORDER BY City;

Output

|   City   |   Country   |
|----------|-------------|
|  Berlin  |    Germany  |
| Chicago  |      USA    |
|  London  |   England   |
| New York |      USA    |
| Paris    |     France  |

Explanation

The UNION operator is used to combine the result-set of two or more SELECT statements. This SQL code is connecting data from two tables: “Customers” and “Suppliers”. It is returning a list of cities and countries from both tables, but only the distinct (meaning no duplicates) values. The result is ordered by city name.

Oracle

Example

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Output

| COLUMN_NAME(S) |
|----------------|
| Row from table1|
| Row from table2|

Explanation

UNION is used to combine the result-set of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be ordered in the same manner. The UNION operator selects only distinct values by default.

SQLite

Example

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers
ORDER BY city;

Output

| city |
|------|
| Berlin |
| London |
| Madrid |
| Mexico D.F. |
| Paris |
| Sao Paulo |
| Sidney |

Explanation

UNION is used to combine the result set of two or more SELECT statements. It removes duplicate rows from the results. In the example, cities from Customers and Suppliers tables are combined and duplicates are removed. The result is a list of unique cities from both tables, sorted in alphabetical order.