Joins

Now, we will learn how to exploit the relationship we just built. This means that if we have the primary key from one table, we can recover all the data needed from that table and also all the linked rows from the child table. To achieve this, we will use something called a join.

A join is basically a way to retrieve linked rows from two tables using any kind of primary key – foreign key relation that they have. There are many types of join, including INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS. They are used in different situations. However, most of the time, in simple 1: N relations, we end up using an INNER join. In Chapter 1, Introduction to Data Wrangling with Python, we learned about sets. We can view an INNER join as an intersection of two sets. The following diagram illustrate the concepts:

Figure 8.7: A diagram representing the intersection join

Here, A represents one table, and B represents another. The meaning of having...