Door 12 UNION

Today, I would like to explain the term UNION in SQL in detail. With UNION, we can combine results from multiple tables. To demonstrate this, we will create another table, because in a film, there are not only leading roles, as we have in the Rols2 table, but also supporting roles. These also have a role name and an actor, and then we assign the film to them as well.

We now want to combine these two tables in the result. We want to know which actors appeared in the individual films, regardless of whether they played a leading or supporting role. To do this, we formulate our UNION statement. This consists of two SELECT-FROM parts that are then connected to each other. In the first SELECT FROM part, we want the fields Name, Actor, and Movie from the Rols2 table. In the second part, we want the fields Name, Actor, and Movie from the table Supporting_roles. We now combine these two queries by writing the term UNION between them. Since we now get all actors back, this can become quite confusing. For this reason, we want to sort the whole thing according to the individual films. The query then looks like this:

SELECT Name, Actor, Movie 
FROM Rols2 
UNION 
SELECT Name, Actor, Movie 
FROM Supporting_roles 
ORDER BY Movie

This gives us the following result:

But maybe we only want to have one movie in the issue. For this, we need the WHERE clause. Since we have two SELECT parts that make a selection, we can also work with two WHERE parts. So we filter for the desired movie, in this case the second one. That's why our query looks like this:

SELECT Name, Actor, Movie 
FROM Rols2 
WHERE Movie='2' 
UNION 
SELECT Name, Actor, Movie 
FROM Supporting_roles 
WHERE Movie='2'

With some roles, it's a bit tricky. They don't occur very often, but they play an enormously important role in the plot. This is the case with Brian Doyle-Murray, who stars in the film Christmas Vacation. He has been listed once in the table of leading roles and once in the table of supporting roles. The simple UNION now only shows him once. This is because UNION sorts out all multiple records and only allows one record in the result set. With a UNION ALL, it's different; here, multiple records are also included in the result set. Let's use UNION ALL here in the same way as Union, so that our script step looks like this:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Actor, Movie FROM Rols2 WHERE Movie='2' 
    UNION ALL SELECT Name, Actor, Movie FROM Supporting_roles WHERE Movie='2' 
    ORDER BY Name" ) ]

We see Brian Doyle-Murray listed twice here in the role of Clark's boss.

That brings us to the end of today's episode, and I hope to see you again tomorrow.