Joining tables using UNION joins.

By Alan Hylands — 3 minute read

Stacking them up.

While the other joins we've looked at are used to link two tables sideways, the UNION join allows us to combine them by setting one table on top of another.

We use our trusty old friend, the SQL SELECT statement, to pull two recordsets from the tables and use UNION to do the “setting on top of”.

A few ground rules for UNION joins.

The two tables need to follow these rules however:

  • Each SELECT statement MUST have the same number of output columns. You can’t SELECT a, b FROM TABLE 1 and only SELECT a FROM TABLE2.
  • The datatypes in the columns have to be the same. Can’t have a text string column from one table and a column of numbers from another.
  • The columns in both SELECT statements have to be in the same order. Don’t have SELECT colA, colB in the first SELECT then SELECT colB, colA in the second.

The UNION join will then stack both datasets together and remove any duplicates rows.

Let's see an example.

First the data tables, our trusty Cities table:

CityIDCityNameCountryID
1NEW YORK1
2LONDON2
3MELBOURNE4
4PARIS5
5ROME

And a table of cities that have held multiple Summer Olympic Games. We’ll call it Olympics:

CityName
LONDON
TOKYO
LOS ANGELES
PARIS
ATHENS

Our SQL code for this UNION join would be:

SELECT CityName from Cities 
UNION
SELECT CityName FROM Olympics;

And the output:

CityName
NEW YORK
LONDON
MELBOURNE
PARIS
ROME
TOKYO
LOS ANGELES
ATHENS

You’ll notice that London and Paris appear in both tables but our UNION join removes the duplicate entries.

Another variation is to use UNION ALL.

While UNION removes duplicates from it’s output, UNION ALL doesn’t. So if the record exists in the first SELECT statement and the second, they will both appear in your results. Just be careful there if you need to watch out for de-duplication.

UNION ALL SQL code looks like this:

SELECT CityName from Cities 
UNION ALL
SELECT CityName FROM Olympics;

And our example output (now also including our duplicates) looks like this:

CityName
NEW YORK
LONDON
MELBOURNE
PARIS
ROME
LONDON
TOKYO
LOS ANGELES
PARIS
ATHENS

London and Paris are back in there twice along with all of the other cities in both our Cities table and our Olympics table.

Quiz Time.

The Question.

Ignore the terrible database design but we have two tables - one has actors who appeared in the Captain America: Civil War movie and another table containing a list of actors who appeared in the Avengers: Endgame movie.

Your task is to get a list of the names of all actors who appeared in either movie - with no duplicate records.

The Data.

The Editor.


Run SQL


Show Answer

The Answer.

Correct SQL:

                    SELECT name FROM cap_actors UNION SELECT name FROM avengers_actors
                    

Correct Output:

Try the next lesson