<jupyter_start><jupyter_text>IntroductionYou have the tools to obtain data from a single table in whatever format you want it. But what if the data you want is spread across multiple tables?That's where **JOIN** comes in! **JOIN** is incredibly important in practical SQL workflows. So let's get started. ExampleWe'll use our imaginary `pets` table, which has three columns: - `ID` - ID number for the pet- `Name` - name of the pet - `Animal` - type of animal We'll also add another table, called `owners`.  This table also has three columns: - `ID` - ID number for the owner (different from the ID number for the pet)- `Name` - name of the owner- `Pet_ID` - ID number for the pet that belongs to the owner (which matches the ID number for the pet in the `pets` table)![](https://i.imgur.com/Rx6L4m1.png) To get information that applies to a certain pet, we match the `ID` column in the `pets` table to the `Pet_ID` column in the `owners` table.  ![](https://i.imgur.com/eXvIORm.png)For example, - the `pets` table shows that Dr. <NAME> is the pet with ID 1. - The `owners` table shows that <NAME> is the owner of the pet with ID 1. Putting these two facts together, Dr. <NAME> is owned by <NAME>.Fortunately, we don't have to do this by hand to figure out which owner goes with which pet. In the next section, you'll learn how to use **JOIN** to create a new table combining information from the `pets` and `owners` tables. JOINUsing **JOIN**, we can write a query to create a table with just two columns: the name of the pet and the name of the owner. ![](https://i.imgur.com/fLlng42.png)We combine information from both tables by matching rows where the `ID` column in the `pets` table matches the `Pet_ID` column in the `owners` table.In the query, **ON** determines which column in each table to use to combine the tables.  Notice that since the `ID` column exists in both tables, we have to clarify which one to use.  We use `p.ID` to refer to the `ID` column from the `pets` table, and `o.Pet_ID` refers to the `Pet_ID` column from the `owners` table.> In general, when you're joining tables, it's a good habit to specify which table each of your columns comes from. That way, you don't have to pull up the schema every time you go back to read the query.The type of **JOIN** we're using today is called an **INNER JOIN**. That means that a row will only be put in the final output table if the value in the columns you're using to combine them shows up in both the tables you're joining. For example, if Tom's ID number of 4 didn't exist in the `pets` table, we would only get 3 rows back from this query. There are other types of **JOIN**, but an **INNER JOIN** is very widely used, so it's a good one to start with.  Example: How many files are covered by each type of software license?GitHub is the most popular place to collaborate on software projects. A GitHub **repository** (or **repo**) is a collection of files associated with a specific project.  Most repos on GitHub are shared under a specific legal license, which determines the legal restrictions on how they are used.  For our example, we're going to look at how many different files have been released under each license. We'll work with two tables in the database.  The first table is the `licenses` table, which provides the name of each GitHub repo (in the `repo_name` column) and its corresponding license.  Here's a view of the first five rows.<jupyter_code>#$HIDE_INPUT$
from google.cloud import bigquery

# Create a "Client" object
client = bigquery.Client()

# Construct a reference to the "github_repos" dataset
dataset_ref = client.dataset("github_repos", project="bigquery-public-data")

# API request - fetch the dataset
dataset = client.get_dataset(dataset_ref)

# Construct a reference to the "licenses" table
licenses_ref = dataset_ref.table("licenses")

# API request - fetch the table
licenses_table = client.get_table(licenses_ref)

# Preview the first five lines of the "licenses" table
client.list_rows(licenses_table, max_results=5).to_dataframe()<jupyter_output><empty_output><jupyter_text>The second table is the `sample_files` table, which provides, among other information, the GitHub repo that each file belongs to (in the `repo_name` column).  The first several rows of this table are printed below.<jupyter_code>#$HIDE_INPUT$
# Construct a reference to the "sample_files" table
files_ref = dataset_ref.table("sample_files")

# API request - fetch the table
files_table = client.get_table(files_ref)

# Preview the first five lines of the "sample_files" table
client.list_rows(files_table, max_results=5).to_dataframe()<jupyter_output><empty_output><jupyter_text>Next, we write a query that uses information in both tables to determine how many files are released in each license.<jupyter_code># Query to determine the number of files per license, sorted by number of files
query = """
        SELECT L.license, COUNT(1) AS number_of_files
        FROM `bigquery-public-data.github_repos.sample_files` AS sf
        INNER JOIN `bigquery-public-data.github_repos.licenses` AS L 
            ON sf.repo_name = L.repo_name
        GROUP BY L.license
        ORDER BY number_of_files DESC
        """

# Set up the query (cancel the query if it would use too much of 
# your quota, with the limit set to 10 GB)
safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)
query_job = client.query(query, job_config=safe_config)

# API request - run the query, and convert the results to a pandas DataFrame
file_count_by_license = query_job.to_dataframe()<jupyter_output><empty_output><jupyter_text>It's a big query, and so we'll investigate each piece separately.![](https://i.imgur.com/QeufD01.png)    We'll begin with the **JOIN** (highlighted in blue above).  This specifies the sources of data and how to join them. We use **ON** to specify that we combine the tables by matching the values in the `repo_name` columns in the tables.Next, we'll talk about **SELECT** and **GROUP BY** (highlighted in yellow).  The **GROUP BY** breaks the data into a different group for each license, before we **COUNT** the number of rows in the `sample_files` table that corresponds to each license.  (Remember that you can count the number of rows with `COUNT(1)`.) Finally, the **ORDER BY** (highlighted in purple) sorts the results so that licenses with more files appear first.It was a big query, but it gave us a nice table summarizing how many files have been committed under each license:<jupyter_code># Print the DataFrame
file_count_by_license<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07818679647094615, 'fraction_numerical': 0.0031944021904472164, 'mean_word_length': 4.539174389216512, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 22, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '410305', 'license': 'Apache-2.0', 'n_tokens_mistral': 1865, 'n_tokens_neox': 1787, 'n_words': 944, 'path': 'notebooks/sql/raw/tut6.ipynb', 'repo_name': 'guesswhohaha/learntools', 'size': '6604'}