How to use SQL UNION

SQL UNION combines records from two or more tables into a target table, without duplicates. The operator helps you merge related records into one table, enabling a better overview of your data and more efficient queries.

What is SQL UNION?

Whether you’re dealing with customer data, price information, product details, project records or financial data, maintaining complex databases always presents a challenge. Part of that challenge is combining records efficiently into meaningful result tables. That’s where the SQL UNION operator comes in. You can use UNION in your queries, comparisons and analyses to combine selected records from one or more tables and remove duplicates.

It can be used with other SQL commands and functions like ORDER BY, GROUP BY, WHERE and HAVING to create relevant data structures based on clear criteria. With aggregate functions like SQL AVG and SQL SUM you can combine tables and calculate and sort result sets.

Tip

Before you take your first steps with SQL you’ll need some knowledge of the basics. Our introduction to SQL with examples provides a good foundation for getting going in SQL.

What is the syntax of SQL UNION?

The basic syntax for SQL UNION looks as follows:

SELECT  ColumnA, ColumnB, … or *  FROM  TableA
UNION
SELECT ColumnA, ColumnB, … or *  FROM  TableA
sql

The following parameters are used:

  • SELECT: Use SELECT to specify which columns you want to bring together. You can use an asterisk * to select all records in the table. You need to select the same number of columns in the same order in each SELECT element.
  • UNION: Combines the selected records from two tables into a single target table.

What is SQL UNION used for?

There is no way around SQL UNION if you need to merge related records like customer files, financial data or product information. Here are some use cases for the UNION operator in SQL:

  • Create complete digital files and folders with customer data, project data, patient information or orders
  • Create sales reports or compare data from different departments
  • Analyse purchase behaviour to generate leads
  • Analyse and compare financial and transaction data
  • Remove duplicates from reports or merged records
  • Evaluate production volumes, shipping quantities or inventories
  • Aggregate order and sales data to identify trends or perform marketing analyses
Tip

Take advantage of secure storage, scalable performance and individual server and hosting offers with SQL Server Hosting from IONOS. Choose from cloud servers, vServers (VPS), dedicated servers or Linux hosting.

VPS Hosting
Fully virtualised servers with root access
  • Unlimited traffic and up to 1 Gbit/s bandwidth
  • Fast SSD NVMe storage
  • Free Plesk Web Host Edition

Examples of SQL UNION

We’ll now look at some examples that show how SQL UNION works in practice.

Combining records into a single table

Let’s say you want to combine the records from two tables called ‘Subscribers’ and ‘Customers’ into a target table without duplicates, using the columns ‘Name’, ‘Address’ and ‘CustomerID’. That would look as follows:

SELECT  Name, Address, CustomerID
FROM  Subscribers
UNION
SELECT  Name, Address, CustomerID
FROM  Customers
sql

Combining employee data from different departments

In this example, you want to combine employee data from tables called ‘Marketing’ and ‘Sales’ using columns like ‘Name’ and ‘City’. That would look as follows:

SELECT  Name, City
FROM  Marketing
UNION
SELECT  Name, City
FROM  Sales
sql

Combing records with UNION ALL

We’ll now turn to an example where we do not want to exclude duplicates. You can use UNION ALL to specify that a target table should contain all records including duplicates. For this example, we’ll take two tables named ‘Customers’ and ‘Suppliers’ and make a new target table that includes all customers and suppliers located in Aberdeen.

SELECT  Name, City
FROM  Customers
WHERE  City  =  'Aberdeen'
UNION ALL
SELECT  Name, City
FROM  Suppliers
WHERE  City  =  'Aberdeen'
GROUP BY  City;
sql

What are the alternatives to SQL UNION?

There are several other options for combining data in SQL. Here are some of the most common options:

  • JOIN: You can use SQL JOIN to merge, query and join data. It includes commands like INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN and CROSS JOIN. JOIN statements offer you more flexibility and customisation than UNION, thanks to the option to add ON clauses.
  • GROUP BY: GROUP BY allows you to combine selected records with identical values in a target table.
  • ORDER BY: ORDER BY enables you to specify which order you want records to appear in in a target table.
  • SELECT DISTINCT: SQL SELECT DISTINCT is similar to UNION and returns only unique query results without duplicates in a target table.
  • UNION ALL: UNION ALL combines records much like UNION but returns all records, including duplicates.
Was this article helpful?