Back to articles list May 28, 2021 - 5 minutes read UNION vs. UNION ALL in SQL: What’s the Difference? Andrew Bone Andrew is a technical writer from Dublin, Ireland. He started his career as a full-stack software developer. However, over time, he found himself enjoying writing documentation and user guides more than programming. He is now focused on breaking down complex subjects so that the writing is clear and understandable, regardless of the readers’ technical background. Tags: sql learn sql union Difference? 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. 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: namelocation JohnFair Street MaryFair Street PaulWest Street Similarly, the rowing_club contains the name of club members and what location they attend. namelocation MaryFair Street SamanthaFair Street PaulWest 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: namelocation JohnFair Street MaryFair Street PaulWest Street SamanthaFair Street There we have it! All of the members from both clubs! 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: namelocation JohnFair Street MaryFair Street PaulWest Street MaryFair Street SamanthaFair Street PaulWest 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. 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! Tags: sql learn sql union You may also like Introducing SQL Set Operators: Union, Union All, Minus, and Intersect Ever heard about union and intersection in SQL? They're set operators that come in handy when you need to combine information from multiple tables. Read more Can you Join two Tables Without a Common Column? Do you need to combine two tables without a common column? Learn how the CROSS JOIN and the UNION operators can help you with this task. Read more How Do You Write a SELECT Statement in SQL? Let’s start with the basics. Learn the syntax of a SELECT statement with simple examples, and start querying databases right away. Read more What Is a Nested Query in SQL? Have you ever faced a problem where it would be great to put a SELECT inside another SELECT statement? Learn how to use a nested query in SQL. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.