Difference: SQL UNION vs UNION ALL ?

UNION and UNION ALL are both used to retrieve records from multiple tables. This article will detail the differences between the two, allowing you to make the best choice for each unique scenario.

You can use SQL’s UNION and UNION ALL commands to get data from multiple tables in your database. It’s a common use case, considering that most databases have many tables. Both UNION and UNION ALL are known as set operators. In SQL, set operators combine the results of two or more queries into a single result. You can read more about set operators in this article.

When comparing UNION vs. UNION ALL, there is one major difference:

• UNION only returns unique
• UNION ALL returns all records, including duplicates.

If you feel like you would benefit from a well-structured, comprehensive course that covers foundational SQL, consider this SQL Basics course from LearnSQL.

UNION vs. UNION ALL: Syntax

The syntax of both UNION variants is very similar. Let’s look at them individually, starting with UNION.

UNION Syntax

Remember, UNION combines the result set of two or more SELECT statements, showing only distinct values.

The SQL syntax below shows a UNION occurring between two different tables; the columns in both SELECT statements are of the same or matching data types.

The WHERE clause shown is an optional inclusion:

SELECT column_1, column_2 FROM table_1 [WHERE condition]   UNION   SELECT column_1, column_2 FROM table_2 [WHERE condition]

SQL UNION ALL Syntax

Remember, UNION ALL combines the results of two or more SELECT statements, showing all values, including duplicates if they exist.

The SQL syntax below shows a UNION ALL occurring between two different tables. Once again, the columns in the SELECT are of the same or matching data types and the WHERE clause is optional:

SELECT column_1, column_2 FROM table_1 [WHERE condition]   UNION ALL   SELECT column_1, column_2 FROM table_2 [WHERE condition]

SQL UNION and UNION ALL Explained

There are several usage rules for UNION and UNION ALL. Failure to adhere to these rules will result in error messages:

• The number of columns used in your first query and your second query must be the same and the data types (e.g. INT, VARCHAR, etc.) must match.
• The column names included in both queries can differ; when this is the case, the resulting dataset will show the column names from the first query.
• When using SQL aliases in conjunction with UNION and UNION ALL, you only need to include the alias with the first query. Including it with the second query will not cause an error, but it will also have no impact on the output.

You can use UNION and UNION ALL to combine tables that do not have any columns in common. This use case is covered in this article on how to join tables without a common column.

LearnSQL.com provides a comprehensive learning experience when it comes to learning SQL. With 30 interactive courses at various levels, the platform will help you to learn the skills you need to tackle reporting in SQL with confidence.

SQL UNION vs UNION ALL: Example

Let’s look at a practical example to gain greater understanding of UNION vs. UNION ALL. Imagine we have a database with information on several clubs. Each table will show the members of that club and what location they attend. Each club may have several different branches or locations where they meet. The first table is for the book_club. It contains the name of club members and what location they attend:

name	location
John	Fair Street
Mary	Fair Street
Paul	West Street

Similarly, the rowing_club contains the name of club members and what location they attend.

name	location
Mary	Fair Street
Samantha	Fair Street
Paul	West Street

Now that we have our tables, let’s write our SQL query. Using the UNION syntax, we get the following query:

SELECT name, location FROM book_club   UNION   SELECT name, location FROM rowing_club

UNION first performs a sorting operation and eliminates the records that are duplicated across all columns before finally returning the combined data set. You can see that we use columns of the same data type and SELECT the same number of columns in each query. This follows the rules of using UNION and should execute without an error.

Running this query produces the following result:

name	location
John	Fair Street
Mary	Fair Street
Paul	West Street
Samantha	Fair Street

There we have it! All of the members from both clubs!

Want to learn SQL? Check out our SQL From A to Z track!

Now let’s alter the query and change UNION to UNION ALL:

SELECT name, location FROM book_club   UNION ALL   SELECT name, location FROM rowing_club

With UNION ALL in place, we can expect the results to include duplicates. Let’s execute this new query and observe the output:

name	location
John	Fair Street
Mary	Fair Street
Paul	West Street
Mary	Fair Street
Samantha	Fair Street
Paul	West Street

Can you spot all of the duplicate records? Compare this output to our original two tables. You can see that every row was included.

UNION or UNION ALL – Which Should You Choose?

Now that you know how to use both UNION and UNION ALL, you’re probably wondering which one is best for your situation. One thing to consider is the fact that UNION removes duplicate records, which can impact the performance of your query.

If you’re wondering which variant to use, remember:

• Using UNION effectively performs a SELECT DISTINCT on the results set.
• If you know that all of the records returned by UNION are going to be unique, use UNION ALL; it will be faster. This is especially relevant for larger datasets.

Do you want to learn all the basics of SQL in one place? Go through our SQL From A to Z track!

Write Complex Queries with UNION and UNION ALL

We’ve covered the differences between UNION and UNION ALL and why you might choose one or the other. With this information, you can now retrieve data from two or more tables in your database. Both UNION and UNION ALL play an important role in the use of recursive queries, a more advanced topic that is covered in great detail in our Recursive Queries course.

Don’t forget to bookmark this page so you can have easy access to the explanations and examples of UNION vs. UNION ALL. Also, consider saving this page of our SQL Cookbook, which explains how to combine the results of two queries in SQL. This knowledge can take your SQL queries to the next level, helping you handle complex use cases with ease!