SQL Union

When you’re querying a database, you may want to combine the result of two or more SELECT statements. For example, you may want to get a list of the cities in which your customers are based, and the cities in which your business has branches. You could run two queries to achieve this goal, but you may want to have a result in one query.

That’s where the SQL UNION operator comes in. The UNION clause can be used to combine the results of two or more SELECT queries into a single result set.

In this tutorial, we are going to break down the basics of the SQL UNION operator and discuss where you may want to use this command when you’re working with a database.

Query Refresher

Programmers use queries to retrieve information from a database. Queries almost always begin with the SQL SELECT statement and are used to retrieve data based on a set of criteria. Queries usually include the FROM operator, which states which table will be queried, among other operators that can filter the resulting data.

Here is the general syntax for an SQL query:

SELECT column_name FROM table_name WHERE your_condititions_are_met;

Here’s an example of an SQL query that returns a list of all employee names:

SELECT name FROM employees;

The output from our query is as follows:

(7 rows)

If you want to retrieve information from multiple columns, you can so do by separating the column names with a comma. If you want to get data from every column, you can use an asterisk (*) instead, which represents every column in a table.

SQL Union

The SQL UNION operator can be used to combine the results of two or more queries into a single response that results in one table.

In order to use the UNION operator, two conditions must be met. Firstly, the data types of the new columns should be compatible—if a salary is an integer in one table and a float in the other, the union would not work. Secondly, the number and order of the columns must be the same in your queries.

Here’s the syntax for an SQL UNION query:

SELECT column_name FROM table1_name
UNION SELECT column_name FROM table2_name;

Let’s use an example to illustrate how the SQL UNION operator works. Let’s say that we are a business that needs to send an announcement to all of our customers. We also want all employees to be sent this announcement so they are aware of what is going on.

We could use the following SQL query to get a list of the emails of both our customers and our employees so we can send them all the announcement:

SELECT name, email FROM employees
UNION SELECT name, email FROM customers;

Here is the output from our query:

(14 rows)

As you can see, our UNION query returned a list of all the names and email addresses for both our customers and our employees.

It’s worth noting that the UNION operator removes duplicate rows from the combined final result. This means that if one of our employees was also a customer, we would only see their information once.

While this worked out in the above example, if you want to return a result including duplicate rows, you would need to add the ALL keyword to your query. Here’s an example:

SELECT name, email FROM employees
UNION ALL
SELECT name, email FROM customers;

Conclusion

In this tutorial we’ve broken down how to use the UNION operator on an SQL server. As we‘ve discussed, UNION can be used to get data from two tables and combine the response into a single table. For example, if you wanted to get a list of your suppliers’ and distributors’ addresses—which were both stored in separate tables—you could use a UNION query.