Different Types of SQL Joins: Concepts and Step-by-Step Thought Process
If you’re a beginner SQL-er and confused about the different joins — look no further! I’ll help you distinguish each join and what they do. We’ll look into the fundamental concepts as well as how to write the syntax. Let’s begin!
First off, let’s create a hypothetical situation and imagine a database for an online store. There are three different tables: customers, orders, and products.
As you can see from the visual above, each table contains specific information. The customers table contains information about the customers, the orders table about orders, and the products table about the products.
Here’s a look inside each table:
When we do a specific SQL join, we are joining or combining two or more tables. Let’s look at what each join does.
Note: if you’d like to follow along, you can download the CSV files at this Github repo.
INNER JOIN
Situation 1: List the first and last names of customers and their order id.
An inner join matches the records between two or more tables that you are joining or combining. According to Situation 1, we want to return the first and last names of the customers as well as their order ids.
Step 1: Decide which tables we need.
Since we need to return the first and last names of customers, we will need to access the customers table. Additionally, we need to list the customers’ order ids thus needing the order table. We will place this in our FROM and JOIN statements. In this case, the order of the table that you call does not matter — they are interchangeable.
Step 2: See which column relates to both tables.
We need to join these two tables off of something they can both relate to. When viewing the customers and orders table, both contain customer_id — this will be what we’re joining on. This will be our ON statement.
Step 3: Decide what we need to return.
We need to decide exactly what we need to return and which tables they’re coming from. Both first_name and last_name originate from the customers table and order_id originates from the orders table. This will be our SELECT statement. Note: even though the SELECT statement comes first, I personally like to decide what needs to be returned last. Do this process in whichever order works best for you.
Now that we have all of the pieces — accio code!
SELECT c.first_name, c.last_name, o.order_id
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.customer_id;Output:
But here’s the catch: an inner join will only return the matched records between these tables. If you want it to return all of the records from one of the tables and still combine it with another, you will have to perform a different join.
LEFT JOIN
Situation 2: List the first and last names of all customers and their order id.
A left join will return all of the records based on the left table and will match it against the right table. But which table is the left table and which is the right table?
This is determined on which table you call first. The table in your FROMstatement is your left table. The table in your JOIN statement is your right table. So, the order that you call the tables is important and does matter — they are not interchangeable.
Situation 2 asks to return all of the first and last names of the customers and their respective order id. Let’s go over our procedure once more.
Step 1: Decide which tables we need.
Since this situation is very similar to Situation 1 and we want to return the same columns, we’ll keep the same tables we used previously: customers and orders.
However, since order matters in a left join, we need to decide which table we have to call first.
Step 1.5: Decide which table to call first (left table).
Since our situation is asking for all customers, we will call our customers table first. This will be our FROM statement. Thus, our JOIN statement will be the orders table (right table).
Step 2: See which column relates to both tables.
Again, we will be joining on customer_id since both tables share this column.
Step 3: Decide what we need to return.
We will need to return first_name and last_name from the customers table and order_id from the orders table. This will be our SELECT statement.
Now that we have all of the pieces — accio code!
SELECT c.first_name, c.last_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id;Output:
What happens if a customer_id is not listed in the orders table? SQL will fill the missing information with a null value. This can be helpful if we want to find any customers that did not place an order.
RIGHT JOIN
Situation 2: List the first and last names of all customers and their order id’s.
A right join is exactly the same as a left join, except we are returning all of the records based on the right table. So, let’s take another look at Situation 2 and how we would solve this problem as a right join instead and returning the same output.
Step 1: Decide which tables we need.
We know now that we are focusing on the customers and orders table.
Step 1.5: Decide which table to call first (left table).
Since this is a right join and our situation is asking for all customers, we will call our customers table second and our orders table first. Remember that our tables are not interchangeable. So, we are switching our tables in our FROM and JOIN statements from before.
Step 2: See which column relates to both tables.
Again, we will be joining on customer_id since both tables share this column.
Step 3: Decide what we need to return.
We will need to return first_name and last_name from the customers table and order_id from the orders table.
Accio code!
SELECT c.first_name, c.last_name, o.order_id
FROM orders AS o
RIGHT JOIN customers AS c
ON o.customer_id = c.customer_id;Output (same as left join):
What if we didn’t switch the tables and changed to a right join like shown below?
SELECT c.first_name, c.last_name, o.order_id
FROM customers AS c
RIGHT JOIN orders AS o
ON o.customer_id = c.customer_id;Output (same as inner join):
Based on what a right join does, this would return all of the records from the orders table and match it against the customers table. If there is a customer_id in the orders table that is not in the customers table, then it will fill out the missing information with a null value. However, all customers should be represented in our customers table regardless and we should not have any null value returned. (This would be ideal and in our case holds true.)
In fact, you may have noticed that the output is the same as the output from our inner join and this is coincidental. If we did have a customer_id in our orders table that was not in our customers table, then they would have been different.
OUTER / FULL JOIN
Situation 3: List the first and last names of all customers, all order ids, and all of the product names.
An outer or full join will return all of the records in all of the tables we call — matched or not. Let’s go through our procedure to write the syntax.
Step 1: Decide which tables we need.
Since we need the first_name and last_name of customers, we’ll need the customers table. We’ll also need the order_id from the orders table and product_name from the products table. So, we’re going to use all three of our tables.
Step 1.5: Decide which table to call first (left table).
Since this is an outer/full join and our situation is asking for all customers, all order ids, and all product names, we can call our tables in whichever order we want — they are interchangeable. This will be in our FROM and JOIN statements. Since we are joining an additional table from before, we will have one more JOIN statement in our code.
Step 2: See which column relates between the tables.
We know now that customer_id relates to both the customers and order table, but what about the products table? Which table can we relate it to? Well, it turns out that product_id is listed in both the orders and products table so we will join these two tables based on that. Since we are doing an additional join, we will have one more ON statement than we did before.
Step 3: Decide what we need to return.
We will need to return first_name and last_name from the customers table, order_id from the orders table, and product_name from the products table.
Accio code!
SELECT c.first_name, c.last_name, o.order_id, p.product_name
FROM customers AS c
FULL JOIN orders AS o
ON o.customer_id = c.customer_id
FULL JOIN products as p
ON o.product_id = p.product_id;Output:
As you can see from our output table, we have all of the customers listed, their respective order_id (if they have one), and all products listed. Nulls are placed for any information that was not matched. In this case, no one purchased a Gray Long-sleeve Shirt, a Blue Denim Skirt, or Black Leggings. Additionally, Stephen Strange, Buffy Summers, and Carrie Bradshaw have not made any orders, and thus, did not purchase any products.
Summary
You’ve made it to the end! Hopefully you feel a step closer to becoming a SQL expert now that you understand SQL joins.
Quick recap:
- Inner join: matches records between two or more tables
- Left join: lists all of the records of the left table and matches it against the other table(s)
- Right join: lists all of the records of the right table and matches it against the other table(s)
- Outer / Full Join: lists all of the records of all tables called
- Any missing information that is not matched will be replaced with a null value
There is one other join that is important, which is a self join, but I will discuss this separately since I think it deserves its own post. Happy SQL-ing!
Feel free to connect with me on LinkedIn!
