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.
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
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:
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 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:
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.
FREE Python Fundamentals Workshop
Use the calendar below to reserve your seat.
Demand for people who know Python is soaring! In this free online workshop, learn the fundamentals of Python and meet other Career Karma members who are building with Python.
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;
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