The SQL Savant: Outer Joins in SQL
Tags: sql, datascience, postgres, dataanalysis
Published: 2020-12-31 18:29:55

---
title: The SQL Savant: Outer Joins in SQL
published: true
description: 
tags: #sql #datascience #postgres #dataanalysis
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/4g55l1ib0bn4uf205fld.jpg
---

Amazing New Year!!! :grinning: [So, the series of meetings with the new Javascript teacher](https://dev.to/joyadauche/the-sql-savant-inner-joins-in-sql-37ak) 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](https://dev.to/joyadauche/the-sql-savant-inner-joins-in-sql-37ak), the class has a database with the **person** and **grade** tables as below:
{% gist https://gist.github.com/joyadauche/82a9d31179dde65c2c7548fdad22d127 %}

Applying Left Joins to above, the table with all students whether they have a grade or not looks like below:
{% gist https://gist.github.com/joyadauche/a2a31812f9412dba0267fa3af7c19198 file=1.md %}

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:
{% gist https://gist.github.com/joyadauche/a2a31812f9412dba0267fa3af7c19198 file=2.sql %}

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:

{% gist https://gist.github.com/joyadauche/a2a31812f9412dba0267fa3af7c19198 file=3.sql %}

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:

{% gist https://gist.github.com/joyadauche/a2a31812f9412dba0267fa3af7c19198 file=4.sql %}

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](https://dev.to/joyadauche/the-sql-savant-inner-joins-in-sql-37ak).

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! :wink: