031 SQL UNION

The current webpage explains the SQL UNION operator. Here’s a concise summary:

Purpose: The UNION operator combines the result sets of two or more SELECT statements into a single result set, automatically removing duplicate rows.
Requirements:
- Each SELECT in the UNION must have the same number of columns.
- Columns must have similar data types.
- Columns must appear in the same order in each SELECT.

Syntax Overview:

Basic form:

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

Notes:
- The resulting column names usually match those in the first SELECT statement.
- UNION returns only distinct values; to include duplicates, use UNION ALL.
Examples:
- Combining city lists from Customers and Suppliers tables, returning only unique city names.
- Filtering with WHERE clauses to, for example, combine German cities from both tables.
- Using aliases and combining records from different tables (listing both customers and suppliers in one query).

The page also includes a sample database (Northwind), sample data tables, explanations of alias use (temporary column names), and interactive SQL examples for practice.[1]

SQL UNION Operator

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

The UNION operator automatically removes duplicate rows from the result set.

Requirements for UNION:

Every SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in every SELECT statement must also be in the same order

UNION Syntax

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

Note: The column names in the result-set are usually equal to the column names in the first SELECT 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:

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

Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use [UNION ALL](https://www.w3schools.com/sql/sql_union_all.asp) 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:

SELECT City, Country FROM Customers  
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:

SELECT 'Customer' AS Type, ContactName, City, Country  
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".