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.
When comparing UNION vs. UNION ALL, there is one major difference:
UNIONonly returns uniqueUNION ALLreturns all records, including duplicates.
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:
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:
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
UNIONandUNION 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.
Did you learn anything from this article? Leave a comment below!
