Marvel Movies, Game of Thrones, SQL, and You

Marvel Movies, Game of Thrones, SQL, and You

Stick with me, there is a SQL story here.

The Marvel Cinematic Universe (MCU) spans 11 years and 22 movies. Over half of U.S. adults aged 18-34 have seen at least one Marvel movie (Statista, 2018).

In an 8-year run, HBO’s Game of Thrones (GoT) has seen television bend the knee with viewership numbers including an impressive 17.4 million for the final season premier (Hibberd, 2019). With the MCU having set its capstone and GoT in its final two episodes, we have a fun opportunity to practice a little set logic as it applies to SQL Inner and Outer Joins.

Set MCU is people who have watched at least 1 MCU movie. Set GOT will contain people who have watched at least 1 episode of GoT.

Imagine we want to create a new group derived from sets MCU and GOT of everyone who has at seen at least 1 MCU movie AND at least 1 GoT episode. This is an Inner Join and looks like this:

SELECT MCU.*, GOT.*
FROM MCU
INNER JOIN GOT
ON MCU.key = GOT.key

Outer Joins in SQL offer two versions and first is a Full Outer Join. This is a union of all MCU with all GOT. Here’s the query:

SELECT MCU.*, GOT.*
FROM MCU
FULL OUTER JOIN GOT
ON MCU.key = GOT.key

If we want to create a set who watched GoT or MCU but not both, then this Outer type looks like this:

SELECT MCU.*, GOT.*
FROM MCU
FULL OUTER JOIN GOT
ON MCU.key = GOT.key
WHERE MCU.key Is Null or GOT.key Is Null

SQL also offers Left and Right Joins with a variation each but understanding Inner and Outer Joins will serve as a firm foundation for all your query work.