Articles Cookbook
Back to articles list
- 7 minutes read

4 Simple Ways to Finally 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:

newborns
idmother_namegenderweightheightmidwife_id
11Marta StewartMale3.452.0104
12Cathrine BlackFemale3.048.5101
13Julia GreyFemale2.848.1104
14Penelope GreenMale3.852.5105
15Gabriela GarciaMale2.948.8105
16Nikita MooreMale4.053.0102
17Lucy MillerFemale2.547.3101

midwives
idfirst_nameyears_experience
101Samantha White6
102Kathleen Webster2
103Sarah McCarty1
105Julia Smith4
106Angela Phillips0

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:

idmother_namemidwife
12Cathrine BlackSamantha White
16Nikita MooreKathleen Webster
17Lucy MillerSamantha White
14Penelope GreenJulia Smith
15Gabriela GarciaJulia Smith

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:

idmother_namemidwife
12Cathrine BlackSamantha White
14Penelope GreenJulia Smith
11Marta StewartNULL
13Julia GreyNULL
17Lucy MillerSamantha White
16Nikita MooreKathleen Webster
15Gabriela GarciaJulia Smith

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:

idmother_namemidwife
12Cathrine BlackSamantha White
14Penelope GreenJulia Smith
15Gabriela GarciaJulia Smith
16Nikita MooreKathleen Webster
17Lucy MillerSamantha White
NULLNULLSarah McCarty
NULLNULLAngela Phillips

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;
idmother_namemidwife
11Marta StewartNULL
12Cathrine BlackSamantha White
13Julia GreyNULL
14Penelope GreenJulia Smith
15Gabriela GarciaJulia Smith
16Nikita MooreKathleen Webster
17Lucy MillerSamantha White
NULLNULLSarah McCarty
NULLNULLAngela Phillips

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.
  1. 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:
  1. 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.
  1. 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!

go to top