4 Ways to Learn Which JOIN to Use in a SQL Query

You probably already know that you should use JOIN to combine data from several tables. But what kind of JOIN? In this article, I’ll introduce four simple ways to finally learn which JOIN to use in different scenarios.

Before we jump into smart strategies for learning SQL JOINs, I suggest taking a brief overview of the major JOIN types. We’ll go through several examples to recall the difference between (INNER) JOIN, LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, and FULL (OUTER) JOIN.

SQL JOIN Types: A Brief Overview

For our JOIN examples, we’ll be using data from a birth center, a midwife-led healthcare facility for mothers in labor. In particular, we have two tables:

1. newborns, which has some basic information on the babies born during a certain period.
2. midwives, with the names and experience information on the midwives working in our birth center.

Here are the tables:

Now, let’s move straight to the examples.

INNER JOIN

We’ll start with the most common JOIN type, which is INNER JOIN (or simply JOIN). This join type is used to display matching records from both tables.

Let’s say we want to get the newborn’s id, the name of their mother, and also the name of the midwife who was helping during labor. To get this information, we can join the newborns and midwives tables based on the midwife’s ID, which is available in both tables:

SELECT n.id, n.mother_name, m.name AS midwife
FROM newborns n
JOIN midwives m
ON n.midwife_id = m.id;

To reduce typing, we use aliases for table names: n for newborns and m for midwives. We also use a column alias (m.name AS midwife) to get a meaningful column name in the output.

Here is the result:

We got the information we wanted. However, you may notice that some newborns (IDs 11 and 13) are missing from our result table. That means that the corresponding records (i.e. midwife IDs) have not been identified in the second table (i.e. midwives).

If you want to keep all the records from the first table, you should use LEFT JOIN.

LEFT JOIN

LEFT JOIN or LEFT OUTER JOIN is used to display all records from the first (left) table and matching records from the second (right) table. To keep all the newborns in the output, we can use the same query as above, simply replacing JOIN with LEFT JOIN:

SELECT n.id, n.mother_name, m.name AS midwife
FROM newborns n
LEFT JOIN midwives m
ON n.midwife_id = m.id;

And the result:

Now we have all the records from the newborns table displayed in the output. At the same time, there are two records with NULL values in the midwife column. Apparently, the corresponding midwife (ID 104) is not listed in the midwives tables. Maybe she has recently started working at another birth center.

Great! But it’s also interesting to see if there are any midwives in our birth center that have not assisted with recent labors. Let’s find out using RIGHT JOIN.

RIGHT JOIN

RIGHT JOIN or RIGHT OUTER JOIN is used to display all records from the second (right) table and matching records from the first (left) table. If we want to keep all records from the midwives table, we can use the exact same SQL query as before, merely replacing LEFT JOIN with RIGHT JOIN:

SELECT n.id, n.mother_name, m.name AS midwife
FROM newborns n
RIGHT JOIN midwives m
ON n.midwife_id = m.id;

Note that RIGHT JOIN is rarely used because it can be usually replaced with LEFT JOIN. In our case, we could use LEFT JOIN, and switch the order of tables, i.e. putting midwives in the FROM clause and newborns in the LEFT JOIN. The results would be the same as below:

In the resulting table, you can see that there are two midwives without matching records in the newborns table: Sarah McCarty and Angela Phillips. It looks like these two midwives have the least experience and are probably assisting the more experienced midwives without being assigned a major role.

Notice also that we again don’t see all the newborns in the result set. What if we want to keep all records from both tables? This is when FULL JOIN should be used.

FULL JOIN

FULL JOIN or FULL OUTER JOIN is used to keep all the records from both tables. So, if we want to keep all newborns and all midwives in the result set, we can just replace the RIGHT JOIN from our last example with a FULL JOIN. Note also that the order of tables doesn’t matter with FULL JOIN; we can switch the position of the tables and get the same result:

SELECT n.id, n.mother_name, m.name AS midwife
FROM newborns n
FULL JOIN midwives m
ON n.midwife_id = m.id;

Now we have some NULL values in all columns; these indicate that some of the left-table records were not matched in the second table or that some of the right-table records were not matched in the first table.

All these JOIN principles don’t sound too challenging, do they? Still, selecting the correct JOIN to use is often a challenge for beginners. So, let’s discover the best strategies for mastering SQL JOINs.

4 Ways to Learn SQL JOINs

Here are four steps to finally mastering the different types of SQL JOINs.

1. Learn the key differences between the JOIN types. Your first step should be to learn about the four major JOIN types and the very basic rules of applying them:

• JOIN displays only the matching records from both tables.
• LEFT JOIN displays all the records from the left table and matching records from the right table.
• RIGHT JOIN displays all the records from the right table and matching records from the left table.
• FULL JOIN displays all the records from both tables.

2. Read high-quality articles on SQL JOINs. After you’ve learned the basics, we recommend going through the details and use cases for different types of JOINs. I suggest you start with these articles that discuss all major JOIN types and include detailed explanations and examples:

• SQL JOIN Types Explained
• What Is an SQL INNER JOIN?
• What Is LEFT JOIN in SQL?
• What FULL JOIN Is and When to Use It?
• 7 SQL JOIN Examples with Detailed Explanations

3. Use our SQL JOIN Cheat Sheet. Even if you know SQL JOINs fairly well, it’s often helpful to see a brief reminder with the syntax and use cases for different JOIN types. I recommend printing or bookmarking our amazing SQL JOIN Cheat Sheet. It is an illustrated guide to SQL JOINs that you can look up whenever you’re not sure how to use JOIN in your SQL query.

4. Practice! Finally, if you really want to master SQL JOINs, you need lots of practice. You can read about the challenges you may encounter when practicing SQL JOINs. To make your first experience with SQL JOINs as smooth as possible, I suggest starting with LearnSQL.com’s interactive SQL JOINs course. This hands-on course includes 93 coding challenges that cover all important types of SQL JOINs. Specifically, you’ll learn:

• When to use JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
• How to join three or more tables in one query.
• How to filter data with different join types.
• How to join a table with itself.
• What a non-equi join is and when to use it.

You can learn more about this course in this overview article.

Thanks for reading, and happy learning!