Easy Introduction to Joins in SQL - Inner Join, Left Join, Right Join, Full Outer Join

Introduction to SQL Joins

Dylan | Jun 01, 2020

Post Thumbnail

SQL stands for Structured Query Language and is the standard language for relational database management systems. SQL is used to update and retrieve data from a database through queries. SQL is a critical tool in the data science toolkit. Every data scientist needs to be able to accurately store and later retrieve their data for analysis or predictions.

The JOIN clause is one of the most powerful tools for efficiently and accurately querying data in SQL. There are various types of JOIN clauses; however they all work by joining data from different tables together. For example, a school may have a table in their database named Classes and another named Students. In order to create class rosters, they will have to create a query to match each student to their enrolled classes.

Lets see how the INNER JOIN clause could help them create a class roster.

INNER JOIN

The INNER JOIN clause works by taking two tables and joining them together on a shared value. Let’s pretend for this school, each student is only enrolled in a single class. Each row in the Students table has a column called class_id and each row in the Classes table has a column called id to store a unique identifier for each class.

The school is able to record what class each student is enrolled in by entering their class’s unique class identifier. Using an INNER JOIN we can match the records from the Students table to the Classes table if their class_id and id values match.

There’s something important about the INNER JOIN, only the records from sharing a common value in both tables will be matched. If there is a class without any students or any students without a class, they will not be retrieved by the INNER JOIN. Refer to the Venn diagram below.

Inner Join Diagram

Using the same school roster example, the syntax to execute an INNER JOIN query is:

SELECT *
FROM Classes c
INNER JOIN Students s ON c.id = s.class_id;

Note: The order of the tables in this join does not matter. Students could have been written first and Classes second in the query above.

LEFT JOIN

Just like INNER JOINS, LEFT JOINS pair records from different tables by matching their column (key) values; but of course they’re not exactly the same. The difference between these two join clauses lies in how they match values. While INNER JOIN only returns values with matches, LEFT JOIN will return all values from the left table and only the records with matching values from the right table. Take a look at the diagram below.

Left Join Diagram

The syntax for a LEFT JOIN query is:

SELECT *
FROM Classes c
LEFT JOIN Students s ON c.id = s.class_id;

Note: Unlike with INNER JOIN clauses, the order you write your tables in the query matters. Do you want all Student records whether or not they have enrolled in a class, or would you like all classes regardless if they have students in them?

RIGHT JOIN

You may be able to guess how the RIGHT JOIN clause works. It is exactly like the LEFT JOIN but in reverse. It will pull all of the records from the table listed to the right of the clause, even if no matching value for it is found in the other table.

Right Join Diagram

The syntax for a RIGHT JOIN query is:

SELECT *
FROM Classes c
RIGHT JOIN Students s ON c.id = s.class_id;

Note: Some SQL systems do not support RIGHT JOIN clauses, because you can achieve the same result by using a LEFT JOIN clause and placing your tables to the left or right of the clause to suit your needs.

FULL OUTER JOIN

The final join clause to introduce in this article is the FULL OUTER JOIN. This join works by returning all records from both tables, records with matching values found as well as records from both tables where no matching values were found. For a helpful visualization, refer to the diagram below.

Full Outer Join Diagram

The syntax for a FULL OUTER JOIN query is:

SELECT *
FROM Classes c
FULL OUTER JOIN Students s ON c.id = s.class_id

Note: Because everything will be returned, the order you list the tables in does not matter for this clause.

Final Thoughts

I hope you found this introduction to SQL joins insightful. Joins are very powerful tools for combining important data stored across multiple tables. Mastering each of these join clauses will help you take your query writing to the next level. They’re very common clauses and worth learning. If you have any questions remaining about joins in SQL, drop it in the comments below. Until next week, thanks for reading and happy coding from Nimble Coding!

Comments