The SQL Savant: Inner Joins in SQL
Tags: sql, datascience, postgres, dataanalysis
Published: 2020-01-05 05:15:09

---
title: The SQL Savant: Inner Joins in SQL
published: true
description: 
tags: #SQL #datascience #postgresql #dataanalysis
cover_image: https://thepracticaldev.s3.amazonaws.com/i/gi0244j61ljg4zcu47b3.png
---



Ever tried retrieving the data you need from just one table but suddenly realised you need more detail or information about these data which you must get from another table? Joins to the rescue!!! 
You can get that additional detail you need using the power of Joins. With Joins in SQL, you can retrieve or access the information you need from two or more tables. 

Let’s say we are at a Javascript college and they just hired a new teacher who wishes to carry every student along in his class and then he requested the academic detail of every student and these details are located in different tables. This certainly sounds like a task for Joins right? 

Assuming this class maintains a database consisting of 3 tables such as *person*, *grade*, and *activity*. We would most likely have to get the information we need by looking up data in the **person** and **grade** tables using joins.

But here comes a pool of questions. How do we get the needed information using joins? What if there are students without grades? Does the teacher want to see only students with grades or all students whether there is a grade or not? Well, it actually depends on how this new Javascript teacher wants it right? So, we had a meeting with him and he wants to see *only students with grades*. It is in this light we introduce the main types of Joins. They are:
- The inner joins and
- The outer joins

This class has a database with the **person** and **grade** tables as below:
{% gist https://gist.github.com/joyadauche/82a9d31179dde65c2c7548fdad22d127 %}

**THE INNER JOIN** (which is the most common type of join), also referred to as JOIN, is a type of JOIN that returns all rows from both participating tables where the key record of one table is equal to the key records of another table. Using an inner join on the **person** and **grade** table will return *only students with grades* like below:

{% gist https://gist.github.com/joyadauche/f4b00735c7db8d9f2985ceefe2913cfe %}

In *2.md* above, you can see that the inner join has combined both tables ON key columns (which has values) that are common to both tables. It then returns records that contain the selected columns in the SELECT clause - You can see in *1.md*, that the _**id**_ field for _**person table**_ and _**person_id**_ field for _**grade table**_ matches for values of *33CC* and *44DD* only.

The code for completing an INNER JOIN from the *person* table to the **grade** table based on the common values of the key field is shown below:
{% gist https://gist.github.com/joyadauche/501eb93774ef3d700a691cd3ab450a3a %}

In *3.sql* and *2.md*, please note the following:

*3.sql*:
- In the **SELECT** clause, the fields or columns you want to be returned from the tables are listed.
- The *person* table is conventionally called the *left table* because it is the first table in the SELECT clause.
- The *grade* table is conventionally called the *right table* because it is the table you are joining on.
- The **INNER JOIN** keyword can be replaced with the **JOIN** keyword - INNER JOIN is the default if you don't specify the type when you use the word JOIN.
- The ON clause introduces the key fields from each table we would be joining on. The ON clause is used to specify a *join condition* or a *join-predicate*. The *name* of a key field or key column can be the same or vary from one table to another - the key field name in the *grade* table, **person_id**, can also be named **id** like in the *person* table as long as it does not conflict with other column names in the grade table.

*2.md*:
- Regardless of whether the value of the key field appears multiple times in a table, as far as it appears in both tables, the record would be included in the result e.g *33CC* is returned twice for the year 2019 and 2020 respectively.

So, we can gladly use the inner join above and give the new teacher what he asked for.



An ad-hoc request comes in! The new teacher wants to see *students with grades along with their yearly activities*. Herein lies the awesomeness of SQL-  *the ability to combine multiple joins in a single query* - Multiple Joins. Ring a bell? Let us see an example below:
{% gist https://gist.github.com/joyadauche/490d888f9f8b7ed3f2f37cee56036db5 %}

In *4.md* above, we have 3 tables - person, grade and activity - an additional table, activity, which shows the hobbies a student partake in yearly. So we came up with the query below:
{% gist https://gist.github.com/joyadauche/43b3413324962bd1fad6ef54a82514ae %}

In *5.sql* above, **activity.year** i.e *table_name.column_name* is used because the field **year** is common to the *grade* and *activity* tables. If the *table_name* is not specified, an error would be thrown saying it is *ambiguous*. So, *5.sql* returns:
{% gist https://gist.github.com/joyadauche/4d79a69335ddadf508b861ca2f324bba %}

Take a second look at this result! Do you observe something wrong with it? Some *score* field values are wrongly paired with activity and year values - *Zamani* for example, _**the 3rd and 4th rows are incorrect**_. This is so because we did not join on an extra key field, *year*, which is common to the *activity* and *grade* tables. So we modify the query to be:
{% gist https://gist.github.com/joyadauche/235dc9960fd65a3fa1af094cbf6a29e8 %}

which returns the desired result below:
{% gist https://gist.github.com/joyadauche/da8c5df43d4113ffa7cf63b4f368ed58 %}



The thought of how multiple inner joins work can be confusing right? Well, what actually happens like in *7.sql* above is that **every single join produces a single derived table** which is then joined to the next table and on and on. Using the *7.sql* example above:
**JOIN 1**: This is the inner join between the *person* table and the *grade* tables. Let’s call this result derived table one (DT1)
**JOIN 2**: This is another inner join between DT1  and the activity table. The result gotten here is the final result returned by this query


So having delivered what is needed in time, let us refactor what we have written for a healthy codebase. There are several ways the query in *7.sql* could have been written. For example:
{% gist https://gist.github.com/joyadauche/9a8a066398b1c81e3463cacbd6a12db7 %}

The **AS** keyword is used for creating an *alias* - an alias is a temporary name that only exists for the duration of the query. Good use cases for aliases are when:
- You want to make column names or table names more readable
- You want to write less because the names are long. - Example: there is more than one table in your query - so you write **p.id = a.person_id** instead of **person.id = activity.person_id**.

If the key field in *grade* and *activity* tables is also **id** (and not person_id), the SQL code would rather be:
{% gist https://gist.github.com/joyadauche/c264c441bceab8311a7eb399ba309e41 %}

You have seen the ON clause so far but if the key field you are joining on has the same name in both tables, use the **USING** keyword instead.



So, I believe you are now becoming an SQL savant! Oh Gosh! This new Javascript teacher is so demanding! Now, he is asking for *all students' academic details regardless of whether they have a grade or not*. Well! Body no be wood! :grin:  So, we have to push this request to another sprint right? :wink: We would set up a meeting with him in the near future to talk about how he wants this because this sounds like a job for the **OUTER JOINS** and there are 3 kinds.

Stay tuned to this series for my next article on **OUTER JOINS**! Have an amazing and fulfilled week ahead!