Unions

Unions enable us to collect related data from multiple tables. Let’s explain its use case and syntax with a practical example, introducing two new tables: sales_invoice_lines and subscription_invoice_lines.

As you can see they have the same structure, since they describe two variations of the same entity (an invoice line); their description column contains values that tell us what kind of services/goods are being invoiced and its cost (e.g. “Yearly subscription”).

Suppose that we would like to collect all their unique values, across both tables, in a single query: using joins is not an option in this case, because values from the two tables would appear in two different columns; furthermore, we would have to keep in consideration that the same invoice may have only one type of invoice line. All in all, this is not the solution we’re looking for.

This is where unions and their UNION / UNION ALL operators come into play:

SELECT description FROM learnsql.sales_invoice_lines
UNION
SELECT description FROM learnsql.subscription_invoice_lines;

Now we got exactly what we wanted: all unique values in a single column.

The other operator, UNION ALL, behaves in the same way except that it returns all values, not just the unique ones.

SELECT description FROM learnsql.sales_invoice_lines
UNION ALL
SELECT description FROM learnsql.subscription_invoice_lines;

Important: the number of columns selected and their data types must match across all tables combined via unions, otherwise the database will return an error. Their names can be different though, in which case the result will contain the names of the columns stated in the first query.