1. UNION & UNION ALL
In this lesson we'll discover how to combine the results of multiple queries using UNION, and UNION ALL.
2. Two queries
Here's a query which returns a small result set from the album table. We're selecting all three columns, but only where the artist_id is 1 or 3, and the query returns three rows.
Here we have a similar query, returning the same three columns, from a similar table, but only where the artist_id is 1, 4 or 5.
This returns 4 rows.
Notice that this query, and the query before it, both return rows for the artist_id of 1.
Our first query returned 3 rows, and the second query returned 4 rows.
While the example is querying the same table, in real life we may have data in separate tables, which share the same structure, that we need to combine into one result set.
What do we mean by the same structure?
It means we expect that both tables have the same number of columns, the columns are listed in the same order and have similar data types.
How could we combine their results?
3. Combining results
The answer is to use UNION.
We write the first query, then UNION on a new line, then we write the second query below it.
How many rows will this query return?
The query only returns 5 rows, not the 7 we might have expected.
Why?
Well, UNION excludes duplicate rows, and as both queries contained the data for artist_id 1, we only retrieve 2 , and not 4 rows for that artist_id.
4. UNION ALL
Here's a slightly different version of the UNION query we saw previously.
Can you spot the difference?
Instead of UNION to join the two queries, it uses UNION ALL.
How many rows might be returned now?
This time, we return all 7 rows.
UNION ALL returns all the rows from both queries, including duplicates.
5. Creating new column names for final results
In this final example, we show the same UNION ALL query as before, but we provide a new alias for each column.
If we have 2 different tables, with different column names, but the same datatypes in the same order, then we can still combine the results , while providing new common column names for our final result set.
6. Summary
Let's recap.
We can combine queries from the same table, or from different tables, using UNION or UNION ALL.
If we're combining data from different tables, our queries need to SELECT the same number of columns, in the same order, and have the same data types, so that the results can be combined.
If our source tables have different column names, we can ALIAS the column names in our SELECT queries so that the final results share the same column names.
UNION combines results but discards duplicates. This means it can be slower to run, whereas UNION ALL returns all results including duplicates, so it is faster.
7. Let's practice!
Let's try our hand at combining data with UNION, and UNION ALL!