Understanding SQL UNION: Combining Results from Multiple Queries

SQL’s UNION operator is a powerful tool for combining the results of two or more SELECT queries into a single, unified result set. If you ever need to merge information from different tables or queries while removing duplicates, UNION is your go-to solution.

What is UNION?

UNION appends the results of multiple SELECT statements vertically by stacking rows on top of one another. It removes duplicate rows by default to ensure the combined output contains only unique records.

Basic Rules for UNION

To use UNION correctly, keep these key points in mind:

• Each SELECT statement must retrieve the same number of columns.
• The corresponding columns must have compatible data types.
• The columns must appear in the same order in each query.

Basic UNION Syntax

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

This will combine the results from both queries into one result set.

Simple Example: Combining Cities from Two Tables

Consider two tables: Customers and Suppliers, each with a City column. To get a list of all unique cities from both tables, use:

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

This query returns a list of cities without duplicates, ordered alphabetically.

What if You Want to Keep Duplicates? Use UNION ALL

The regular UNION removes duplicates which is great in most cases. But if you want to include all records, even duplicates, use UNION ALL:

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;

This will list every city from both tables, including repeated ones.

Adding a Column to Identify Source

When combining different tables, you might want to know which row came from which table. You can add a literal column using an alias like this:

SELECT ContactName, City, 'Customer' AS Type
FROM Customers
UNION
SELECT ContactName, City, 'Supplier' AS Type
FROM Suppliers;

The result will include a Type column indicating whether the row is for a customer or supplier.

Using UNION with WHERE Clauses

You can add conditions to each part of the UNION separately. For example, returning cities only from Germany in both tables:

SELECT City, Country FROM Customers WHERE Country = 'Germany'
UNION
SELECT City, Country FROM Suppliers WHERE Country = 'Germany'
ORDER BY City;

This filters the results before combining them.

Practical Use Cases for UNION

• Combine datasets from multiple similar tables: For example, merging customer lists from different regions or suppliers divided by categories.
• Merge result sets from different queries: Such as finding all employees who are either in the current team or past team.
• Eliminate duplicates across tables: To get unique sets of values like distinct cities, product names, or customer contacts.

Important Notes on UNION

• The column names in the final result usually come from the first SELECT statement.
• If columns have different names but same types, UNION still works but the output column names follow the first query.
• To combine tables with different columns, you can use NULL or constants to maintain column count, e.g. SELECT Name, City, NULL AS Phone FROM Customers
  UNION
SELECT SupplierName, City, Phone FROM Suppliers;
  

Summary Table of UNION vs UNION ALL

Example Combining Employee Names

Imagine two staff tables, CurrentEmployees and PastEmployees:

SELECT EmployeeName FROM CurrentEmployees
UNION
SELECT EmployeeName FROM PastEmployees;

This lists every unique employee name from both current and past staff.

Conclusion

The SQL UNION operator is a simple but powerful feature to combine multiple query results into one, making it easier to consolidate data across different tables or result sets. Knowing when and how to use UNION vs UNION ALL lets you handle duplicates according to your needs.