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 UNIO
N ALL
are known as set operators.
When comparing UNION
vs. UNION ALL
, there is one major difference:
UNION
only returns uniqueUNION ALL
returns 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
UNION
andUNION 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!