7.3. Joining

To connect records between two data tables, SQL relations can be joined together similar to dataframes. In this section, we introduce SQL joins to replicate our analysis of the baby names data. Recall that Chapter 6 mentions a New York Times article that talks about how certain name categories, like mythological and baby boomer names, have become more or less popular over time.

We’ve taken the names and categories in the NYT article and put them in a small relation named nyt:

# Set up connection to database
import sqlalchemy
db = sqlalchemy.create_engine('sqlite:///babynames.db')

query = ''' 
SELECT *
FROM nyt;
'''

pd.read_sql(query, db)

	nyt_name	category
0	Lucifer	forbidden
1	Lilith	forbidden
2	Danger	forbidden
...	...	...
20	Venus	celestial
21	Celestia	celestial
22	Skye	celestial

23 rows × 2 columns

Note

Notice that the preceding code runs a query on babynames.db, the same database that contains the larger baby relation from the previous sections. SQL databases can hold more than one relation, making them very useful when we need to work with many data tables at once. CSV files, on the other hand, typically contain one data table each—if we perform a data analysis that uses 20 data tables, we might need to keep track of the names, locations, and versions of 20 CSV files. Instead, it could be simpler to store all the data tables in a SQLite database stored in a single file.

To see how popular the categories of names are, we join the nyt relation with the baby relation to get the name counts from baby.

7.3.1. Inner Joins

As in Chapter 6, we’ve made smaller versions of the baby and nyt tables so that it’s easier to see what happens when we join tables together. The relations are called baby_small and nyt_small:

query = ''' 
SELECT *
FROM baby_small;
'''

pd.read_sql(query, db)

	Name	Sex	Count	Year
0	Noah	M	18252	2020
1	Julius	M	960	2020
2	Karen	M	6	2020
3	Karen	F	325	2020
4	Noah	F	305	2020

query = ''' 
SELECT *
FROM nyt_small;
'''

pd.read_sql(query, db)

	nyt_name	category
0	Karen	boomer
1	Julius	mythology
2	Freya	mythology

To join relations in SQL, we use the INNER JOIN clause to say which tables we want to join and the ON clause to specify a predicate for joining the tables. Here’s an example:

query = ''' 
SELECT *
FROM baby_small INNER JOIN nyt_small
  ON baby_small.Name = nyt_small.nyt_name
'''

pd.read_sql(query, db)

	Name	Sex	Count	Year	nyt_name	category
0	Julius	M	960	2020	Julius	mythology
1	Karen	M	6	2020	Karen	boomer
2	Karen	F	325	2020	Karen	boomer

Notice that this result is the same as doing an inner join in pandas: the new table has the columns of both the baby_small and nyt_small tables. The rows with the name Noah are gone, and the remaining rows have their matching category from nyt_small.

To join two tables together, we tell SQL the column(s) from each table that we want to use to do the join using a predicate with the ON keyword. SQL matches rows together when the values in the joining columns fulfill the predicate, as shown in Fig. 7.2.

Fig. 7.2 Joining two tables together with SQL

Unlike pandas, SQL gives more flexibility on how rows are joined. The pd.merge() method can only join using simple equality, but the predicate in the ON clause can be arbitrarily complex. As an example, we take advantage of this extra versatility in Section 12.2.

7.3.2. Left and Right Joins

Like pandas, SQL also supports left joins. Instead of saying INNER JOIN, we use LEFT JOIN:

query = ''' 
SELECT *
FROM baby_small LEFT JOIN nyt_small
  ON baby_small.Name = nyt_small.nyt_name
'''

pd.read_sql(query, db)

	Name	Sex	Count	Year	nyt_name	category
0	Noah	M	18252	2020	None	None
1	Julius	M	960	2020	Julius	mythology
2	Karen	M	6	2020	Karen	boomer
3	Karen	F	325	2020	Karen	boomer
4	Noah	F	305	2020	None	None

As we might expect, the “left” side of the join refers to the table that appears on the left side of the LEFT JOIN keyword. We can see the Noah rows are kept in the resulting relation even when they don’t have a match in the righthand relation.

Note that SQLite doesn’t support right joins directly, but we can perform the same join by swapping the order of relations, then using LEFT JOIN:

query = ''' 
SELECT *
FROM nyt_small LEFT JOIN baby_small
  ON baby_small.Name = nyt_small.nyt_name
'''

pd.read_sql(query, db)

	nyt_name	category	Name	Sex	Count	Year
0	Karen	boomer	Karen	F	325.0	2020.0
1	Karen	boomer	Karen	M	6.0	2020.0
2	Julius	mythology	Julius	M	960.0	2020.0
3	Freya	mythology	None	None	NaN	NaN

SQLite doesn’t have a built-in keyword for outer joins. In cases where an outer join is needed, we have to either use a different SQL engine or perform an outer join via pandas. However, in our (the authors’) experience, outer joins are rarely used in practice compared to inner and left joins.

7.3.3. Example: Popularity of NYT Name Categories

Now let’s return to the full baby and nyt relations.

We want to know how the popularity of name categories in nyt has changed over time. To answer this question, we should:

1. Inner join baby with nyt, matching rows where the names are equal.

2. Group the table by category and Year.

3. Aggregate the counts using a sum:

query = ''' 
SELECT
  category,
  Year,
  SUM(Count) AS count           -- [3]
FROM baby INNER JOIN nyt        -- [1]
  ON baby.Name = nyt.nyt_name   -- [1]
GROUP BY category, Year         -- [2]
'''

cate_counts = pd.read_sql(query, db)
cate_counts

	category	Year	count
0	boomer	1880	292
1	boomer	1881	298
2	boomer	1882	326
...	...	...	...
647	mythology	2018	2944
648	mythology	2019	3320
649	mythology	2020	3489

650 rows × 3 columns

The numbers in square brackets ([1], [2], [3]) in the preceding query show how each step in our plan maps to the parts of the SQL query. The code re-creates the dataframe from Chapter 6, where we created plots to verify the claims of the New York Times article. For brevity, we omit duplicating the plots here.

Note

Notice that in the SQL code in this example, the numbers appear out of order—[3], [1], then [2]. As a rule of thumb for first-time SQL learners, we can often think of the SELECT statement as the last piece of the query to execute even though it appears first.

In this section, we introduced joins for relations. When joining relations together, we match rows using the INNER JOIN or LEFT JOIN keyword and a boolean predicate. In the next section, we’ll explain how to transform values in a relation.