Skip to main content

Unions

Premium

The UNION and UNION ALL operators in SQL allow you to combine the results of two or more SELECT statements into a single result set. The difference between UNION and UNION ALL is that UNION removes duplicates from the result set, while UNION ALL includes all rows, even if they are duplicates.

UNION is one of several set operations that SQL supports, in addition to INTERSECT and MINUS/EXCEPT.

Basic Syntax for UNION

Here's the basic syntax for a UNION:

SQL
SELECT column_name(s)
FROM table1
UNION
SELECT column_name(s)
FROM table2;

And here's the basic syntax for a UNION ALL:

SQL
SELECT column_name(s)
FROM table1
UNION ALL
SELECT column_name(s)
FROM table2;

Note that unlike joins, set operations like UNION always use two separate select statements, or subqueries, and concatenate their results. Each of the select statements should return a result set with the same number of columns, otherwise you'll encounter an error.

When to use UNION

Here are a few practical examples of when to use UNION and UNION ALL:

  • Combining data from two tables with the same structure: For example, you have a customers table and an archived_customers table. You can use UNION to combine the data from these two tables into a single result set for subsequent analysis.

  • De-duplicating data: For example, you have a products table and a duplicate_products table. You can use UNION to combine the data from these two tables into a single result set, with duplicates removed.

In general, UNION and UNION ALL are powerful tools for combining data from multiple tables into a single result set. They are especially useful when you want to combine data from tables with the same structure, or when you want to find duplicates in your data.

Next Lesson