Amazing New Year!!! 😀 So, the series of meetings with the new Javascript teacher went quite well and we got loads of analysis we gotta do...
So right now he wants every student's academic detail whether they got a grade or not which can be easily achieved using a Left Outer Join. Hence, let's talk about OUTER JOINS!
With outer joins, all records from one table are kept even if there are no matches in the other table that it joins on. There are 3 types of outer joins:
- Left Joins
- Right Joins
- Full Joins
With LEFT JOIN, all records from the left table (i.e the left table is the one after the FROM clause) are kept even if there are no matches in the right table (i.e the table after the JOIN type). Remember that from here, the class has a database with the person and grade tables as below:
Applying Left Joins to above, the table with all students whether they have a grade or not looks like below:
| id | name | score | year |
|---|---|---|---|
| 33CC | Zamani | C | 2020 |
| 33CC | Zamani | B | 2019 |
| 44DD | Ayo | A | 2020 |
| 44DD | Ayo | B | 2019 |
| 55EE | Ahmad | null | null |
| 11AA | Adebayo | null | null |
| 22BB | Chioma | null | null |
From above:
- We can see that all records from the person table, which is the left table, are returned,
- Also, the records with null are those with values in the left table (person table) but have no matching record in the right table (grade table),
- The first 4 records are the same as those when we use an inner join while the last 3 corresponds to students that do not have a grade, hence their grade values are null.
The code for the result of the LEFT JOIN above is below:
| SELECT id, name, score, year | |
| FROM person | |
| LEFT JOIN grade | |
| ON person.id = grade.person_id; |
Unlike INNER JOINS that keeps just the records corresponding to the id values of 33CC and 44DD, a LEFT JOIN keeps all of the records in the left table but then marks the values as null in the right table for those that don’t have a match.
Moving on RIGHT JOINS, which just does the reverse of LEFT JOINS. It matches all records via the key column from the right table even if there are matching records in the left table. let's see the code below:
| SELECT id, name, score, year | |
| FROM grade | |
| RIGHT JOIN person | |
| ON grade.person_id = person.id; |
From above, the right table is person while the left table is grade. Since the RIGHT JOIN is just the reverse of the LEFT JOIN, the LEFT JOIN is more commonly used.
Finally, let's talk about FULL JOINS! This type of join combines both the LEFT JOIN and RIGHT JOIN. It combines all the records from the LEFT TABLE and the RIGHT TABLE. For record values that do not match for the left and right tables, the value will be null, as seen in other types of outer joins.
Note that in our example case, the result for the FULL JOIN will be the same as the LEFT JOIN because: for example, when using person as the left table and grade as the right table, all records in the right table match records from the left table i.e there are no records in the right table that cannot be found in the left table, hence it returns all records as seen in a Left Join. Now, let's see the Full Join code below:
| SELECT id, name, score, year | |
| FROM person | |
| FULL JOIN grade | |
| ON person.id = grade.person_id; |
As you can see from above, we just had to change the join type to FULL JOIN. Also, kindly note that we can do multiple joins with any type of outer joins just like we saw here.
Quite simple! so we can share our SQL analysis with the JS teacher and move on to a special kind of join called CROSS JOIN to perform more analyses! Have an amazing and fulfilled week ahead in this new Year! 😉
Top comments (0)