Introduction to SQL Using Python: Using JOIN Statements to Merge Multiple Tables
This blog is a tutorial on how to pull data from multiple tables in SQL. In my previous blog tutorials, data was only queried from a single table at a time. This tutorial will introduce JOIN statements. With JOIN statements, data from multiple tables can be returned in a single query.
This blog will discuss the following:
- LIMIT Statement
- Returning Multiple Tables without a JOIN Statement
- JOIN Statement
- Return Specific Columns with a JOIN Statement
- Filter & Aggregate with Multiple Tables
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
- UNION Statement
We will be using the database used in my previous tutorials which can be downloaded here. If you have not seen my previous tutorials, now would be a good time to preview the data tables available in the Football Delphi database. You can read my first two SQL tutorials, at the below links:
- Introduction to SQL Using Python: Filtering Data with the WHERE Statement
- Introduction to SQL Using Python: Computing Statistics & Aggregating Data
To begin, we will download the necessary libraries, sqlite3 and pandas.
Next, you will need to connect to the database and create a cursor object.
The following is format we will be using to run our SQL queries in Python.
LIMIT Statement
The LIMIT statement will limit the number of rows returned in a SQL query. It is written at the end of a SQL query and the last statement to be executed in the SQL order of operations. To see how the LIMIT statement is used, preview the query below:
The above query returns the first ten rows from the Teams_in_Matches table. To practice using the LIMIT statement, write a query that returns the first ten rows from the Unique_Teams dataset and compare your query to the one below:
Returning Multiple Tables Without a JOIN Statement
The Teams_in_Matches dataset has two columns, Match_ID and Unique_Team_ID. Since we are not familiar with the Unique_Team_ID for each team, it would be easier to identify each team if we could see the name of the team along side the columns in the Team_in_Matches data table. The Unique_Teams data table has two columns, Team_Name and Unique_Team_ID. If we returned both tables together we could see the name of each team next to the Match_ID and Unique_Team_ID columns from the Teams_in_Matches dataset. The below query returns the data from both tables:
The query returned 6,290,944 rows although the Teams_in_Matches table only has 49,148 rows and the Unique_Teams table only has 128 rows. You may have thought that the query would return at most 49,148 rows. What happened was that SQL took every single row from the Teams_in_Matches table and matched it with every single row from the Unique_Teams table, so the query returned 49,148 * 128 = 6,290,944 rows. The returned data we have now is not useful and way too large. If we want SQL to return the Match_ID and Unique_Team_ID with the corresponding Team_Name we have to tell SQL how to return the results. Since Unique_Team_ID is a column name in both tables we can use the query below to get the correct results:
We added a WHERE statement and told SQL how/what column to join together the two tables, WHERE Teams_in_Matches.Unique_Team_ID = Unique_Teams.Unique_Team_ID. The returned results show only the rows where the Unique_Team_ID from the Teams_in_Matches table matched the value of the Unique_Team_ID from the Unique_Teams data table. When working with multiple tables you have to clarify what table each column is coming from, especially when the column names have the same name. In the WHERE statement we wrote the name of the table followed by a period and then the column name, i.e. Teams_in_Matches.Unique_Team_ID.
JOIN Statement
We will run the same query we just used but instead of using a WHERE statement, we will use a JOIN statement. To see how this looks, look at the query below:
Instead of listing the tables we want to use in the FROM statement we first list the table, Teams_in_Matches and then added a JOIN statement. We are telling SQL that we want the data from the Teams_in_Matches table joined together with the data from the Unique_Teams. We also included an ON statement, this tells SQL HOW to join the data together.
The Teams table does not include the Unique_Team_ID for each team. Practice using the JOIN statement by writing a query that joins together the Unique_Teams data table and the Teams table, only return the first 10 rows. Compare your query to the one below:
Return Specific Columns with a JOIN Statement
If we go back and look at our query where we joined together the Teams_in_Matches table and the Unique_Teams table, you will see the the Unique_Team_ID column is listed twice. By specifying the columns we want returned in the SELECT statement, we can get rid of this redundancy.
Again, because we are querying data from multiple tables, we specify what table each column is coming from by writing the table name followed by a period and then the column name (Unique_Teams.TeamName). Practice selecting specific columns when working with multiple tables by writing a query that shows the Unique_Team_ID and TeamName from the Unique_Teams table and AvgAgeHome, Season and ForeignPlayersHome from the Teams table. Only return the first five rows. Compare your query to the one below:
Although it is good practice to write the table name before the column name when querying multiple tables, it is only necessary to do so when there are duplicate column names. The following query will produce the same results as the query above:
Filter & Aggregate with Multiple Tables
We can still use the same filtering methods and aggregation methods discussed in my previous blogs when working with multiple tables. In the last query we see data for Bayern Munich is returned for every season. The following query groups together data by each team and displays the average team player age and max number foreign players for each team from all the seasons after the 2000 season.
The above query is a bit complicated but it’s good to keep in mind that all the techniques discussed from my previous blogs and this blog can be used together to create queries. For now write a query that shows the highest Match_ID for each team that ends in a “y” or a “r”. Along with the maximum Match_ID, display the Unique_Team_ID from the Teams_in_Matches table and the TeamName from the Unique_Teams table. Compare your query to the one below:
INNER JOIN
The JOIN statement we have been using so far is also known as an INNER JOIN. An INNER JOIN is typically the default type of JOIN statement. We have seen the below query earlier but this time, instead of writing JOIN, we write INNER JOIN. The results are the same for both queries.
An inner join takes all the rows from two tables where ever there is a match between two specified columns. If a Match_ID is not assigned a Unique_Team_ID, then the Match_ID will not show up in the results and vice versa, if a TeamName is not assigned a Unique_Team_ID then that TeamName will not appear in the results.
LEFT JOIN
To make the next example easier to understand, we will modify the Unique_Teams data table. Run the query below:
There are various types of JOINs in addition to the INNER JOIN. We will now discuss the LEFT JOIN. A LEFT_JOIN will return all the rows from the left table/the first table listed in the query, and any matching rows from the right table/the second table listed in the query. If there is no match, then the data from the left table will still be displayed and the data from the right table will show up as NaN. To see how a LEFT JOIN works, check out the query below:
The first table, Matches, is the left table so all rows from that table are included. The second table, Unique_Team, is the right table and only when there is a matching row is data included from that table. For example, there is no corresponding row from the Unique_Teams data table where TeamName is Oberhausen or Frankfurt FSV, so the Unique_Team_ID shows up as NaN in those rows. Run the following code before the next example to help make LEFT JOINs easier to understand.
To practice using a LEFT JOIN, return all the rows from the Matches table, if the Home Team in a match has a Unique_Team_ID show the data from the Teams_in_Matches table. Only return the first 10 rows. Compare your query with the one below:
This time, we see that the rows where the Match_ID is 7, 8 and 9, the second Match_ID column and Unique_Team_ID columns are NaN. This means there was no rows in Match_ID from the Teams_in_Matches data table that were 7, 8 or 9.
RIGHT JOIN
Before moving on, run the following code:
The RIGHT JOIN is similar to the LEFT JOIN. A RIGHT JOIN returns all the rows from the right table/the second table listed, and all the matching rows from the left table/the first table listed. To see how this works look at the query below:
The query above won’t run if you are using Jupyter Notebook because RIGHT JOINS are not currently supported. You can go to https://www.w3schools.com/sql/sql_join_right.asp to read more about RIGHT JOINS and practice. The above query should return all the rows from the Unique_Teams table and only the matches rows from the Matches table. This is basically the opposite of the LEFT JOIN and LEFT JOINS are used more commonly used than RIGHT JOINS.
FULL OUTER JOIN
The FULL OUTER JOIN returns all the rows from both the left and right tables and combines the rows when there is a match. It will also return rows when there is no match. It’s kind of like a combination of the LEFT JOIN and the RIGHT JOIN. The query below should return all the rows from the Matches table and the Unique_Teams table.
The above query will not run if you are using Jupyter Notebook but you can read more about FULL OUTER JOINS here, https://www.w3schools.com/sql/sql_join_full.asp.
The below image is a visual representation of the INNER JOIN, LEFT JOIN, RIGHT JOIN and the FULL OUTER JOIN.
UNION Statement
The UNION statement is not a JOIN statement but it is a way to combine multiple tables. The UNION statement combines the results from two tables. The two tables must have the same number of columns and be the same data type. At this point we have deleted some teams from the Matches data table and some teams from the Unique_Teams table. We can use a UNION statement to get a list of all the teams.
The above results combine the two results from SELECT HomeTeam FROM Matches and SELECT TeamName FROM Unique_Teams. Duplicates are not included but if you wished for duplicates to be included, you could write UNION ALL instead of UNION.
You have reached the end of this tutorial. The following items were discussed:
- LIMIT Statement
- Returning Multiple Tables without a JOIN Statement
- JOIN Statement
- Return Specific Columns with a JOIN Statement
- Filter & Aggregate with Multiple Tables
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
- UNION Statement
I encourage you to keep practicing using the above methods to gain a deeper understanding of how they work.