SQL JOIN Types Explained

What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN in SQL? When should you use each one? We’ve got your answers right here.

You want to combine data from two or more different tables, but you’re not sure how to do it in SQL. No worries. In this article, I will show you how to use the SQL JOIN clause to merge data from two tables. There are different SQL JOIN types that you can use for different results. If you want to learn the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, keep reading. This article is for you.

First, let’s start with the basics: what is a JOIN statement and how does it work?

What Is an SQL JOIN?

A JOIN clause is used when you need to combine data from two or more tables into one data set. Records from both tables are matched based on a condition (also called a JOIN predicate) you specify in the JOIN clause. If the condition is met, the records are included in the output.

In this article, I’ll explain the SQL JOIN concept and the different JOIN types using examples. So, before we go any further, let's take a look at the tables that we are going to use in this article.

Get to Know the Database

We are going to use tables from a fictional bank database. The first table is called account and it contains data related to customer bank accounts:

account table

This table contains 10 records (10 accounts) and five columns:

• account_id – Uniquely identifies each account.
• overdraft_amount – The overdraft limit for each account.
• customer_id – Uniquely identifies each customer.
• type_id – Identifies the type of that account.
• segment – Contains the values ‘RET’ (for retail clients) and ‘CORP’ (for corporate clients).

The second table is called customer and contains customer-related data:

customer table

This table contains eight records and five columns:

• customer_id – Uniquely identifies each account.
• name – The customer’s first name.
• lastname – The customer’s last name.
• gender– The customer’s gender (M or F).
• marital_status – If the customer is married (Y or N).

Now that we have these two tables, we can combine them to display additional results related to customer or account data. JOIN can help us to get answers to questions like:

• Who owns each account in the account table?
• How many accounts does Marc Tesco have ?
• How many accounts are owned by a female customer?
• What is the total overdraft amount for all of Emma Johnson’s accounts?

To answer each of these questions, we need to combine two tables (account and customer) using a column that appears in both tables (in this case, customer_id). Once we merge the two tables, we will have account and customer information in a single output.

Keep in mind that in the account table we have some customers that can’t be found in the customer table. (Info about corporate clients is stored somewhere else.) Also, keep in mind that some customer IDs are not present in the account table; some customers don't have accounts.

There are several ways we can combine two tables. Or, put another way, we can say that there are several different SQL JOIN types.

SQL’s 4 JOIN Types

SQL JOIN types include:

• INNER JOIN (also known as a ‘simple’ JOIN). This is the most common type of JOIN.
• LEFT JOIN (or LEFT OUTER JOIN)
• RIGHT JOIN (or RIGHT OUTER JOIN)
• FULL JOIN (or FULL OUTER JOIN)
• Self joins and cross joins are also possible in SQL, but we won’t talk about them in this article. For more info, see An Illustrated Guide to the SQL Self Join and What is CROSS JOIN in SQL.

Let's dive deeper into the first four SQL JOIN types. I will use an example to explain the logic and the syntax of each type. Sometimes people use Venn diagrams when explaining SQL JOIN types. I'm not going to use them here, but if that’s your thing then check out the article How to Learn SQL JOINs.

INNER JOIN

INNER JOIN is used to display matching records from both tables. This is also called a simple JOIN; if you omit the INNER keyword (or any other keyword, like LEFT, RIGHT, or FULL) and just use JOIN, this is the type of join you’ll get by default.

There are usually two (or more) tables in a join statement. We call them the left and right tables. The left table is in the FROM clause – and thus to the left of the JOIN keyword. The right table is between the JOIN and ON keywords, or to the right of the JOIN keyword.

If the JOIN condition is met in an INNER JOIN, that record is included in the data set. It can be from either table. If the record does not match the criteria, it’s not included. The image below shows what would happen if the color blue was the join criteria for the left and right tables:

Let's take a look how INNER JOIN works in our example. I’m going to do a simple JOIN on account and customer to display account and customer information in one output:

SELECT account.*,
      customer.name,
      customer.lastname,
      customer.gender,
      customer.marital_status
FROM account 
JOIN customer 
ON account.customer_id=customer.customer_id;

Here is a short explanation of what’s going on:

• I’m using JOIN because we are merging the account and customer tables.
• The JOIN predicate here is defined by equality:

account.customer_id = customer.customer_id

In other words, records are matched by values in the customer_id column:

• Records that share the same customer ID value are matched. (They are shown in color in the above image.) Records that don’t have a match in either table (shown in gray) are not included in the result set.
• For records that have a match, all attributes from the account table are displayed in the result set. The name, last name, gender, and marital status attributes from the customer table are also displayed.

After running this code, SQL returns following:

INNER JOIN result

As we mentioned earlier, only colored (matching) records were returned; all others are discarded. In business terms, we displayed all the retail accounts with detailed information about their owners. Non-retail accounts were not displayed because their customer information is not stored in the customer table.

LEFT JOIN

Sometimes you’ll need to keep all records from the left table – even if some don't have a match in the right table. In the last example, the gray rows were not displayed in the output. Those are corporate accounts. In some cases, you may want to have them in the data set, even if their customer data is left empty. If we would like to return unpaired records from the left table, then we should write a LEFT JOIN. Below, you can see that the LEFT JOIN returns everything in the left table and matching rows in the right table.

Here is how the previous query would look if we used LEFT JOIN instead of INNER JOIN:

SELECT account.*,
      customer.name,
      customer.lastname,
      customer.gender,
      customer.marital_status
FROM account 
LEFT JOIN customer 
ON account.customer_id=customer.customer_id;

The syntax is identical. The result, however, is not the same?. Now we can see the corporate accounts (gray records) in the results:

Left join - account with customer

Notice how attributes like name, last name, gender, and marital status in the last four rows are populated with NULLs. This is because these gray rows don’t have matches in the customer table (i.e. customer_id values of 50, 51 ,52 , and 53 are not present in the customer table). Thus, those attributes have been left NULL in this result.

RIGHT JOIN

Similar to LEFT JOIN, RIGHT JOIN keeps all records from the right table (even if there is no matching record in the left table). Here’s that familiar image to show you how it works:

Once again, we use the same example. However, we’ve replaced LEFT JOIN with RIGHT JOIN:

SELECT account.account_id,
      account.overdraft_amount,
      account.type_id,
      account.segment,
      account.customer_id,
      customer.customer_id
      customer.name,
      customer.lastname,
      customer.gender,
      customer.marital_status
FROM account 
RIGHT JOIN customer 
ON account.customer_id=customer.customer_id;

The syntax is mostly the same. I’ve made one more small change: In addition to account.customer_id, I’ve also added customer.customer_id column to the result set. I did this to show you what happens to records from the customer table that don't have a match on the left (account) table.

Here is the result:

RIGHT JOIN result

As you can see, all records from the right table have been included in the result set. Keep in mind:

• Unmatched customer IDs from the right table (numbers 2,3, 6,7, and 8, shown in gray) have their account attributes set to NULL in this result set. They are retail customers that don’t have a bank account – and thus no records in the account table.
• You might expect that the resulting table will have eight records because that is the total number of records in the customer table. However, this is not the case. We have 11 records because customer IDs 1, 4, and 5 each have two accounts in the account table. All possible matches are displayed.

FULL (OUTER) JOIN

I’ve shown you how to keep all records from the left or right tables. But what if you want to keep all records from both tables? In our case, you’d want to display all matching records plus all corporate accounts plus all customers without accounts. To do this, you can use FULL OUTER JOIN. This JOIN type will pair all matching columns and will also display all unmatching columns from both tables. Unfamiliar attributes will be populated with NULLs. Have a look at the image below:

Here is the FULL OUTER JOIN syntax:

SELECT account.*,
      CASE WHEN customer.customer_id IS NULL
                 THEN account.customer_id 
                 ELSE customer.customer_id 
       END customer_id
      customer.name,
      customer.lastname,
      customer.gender,
      customer.marital_status
FROM account 
FULL JOIN customer 

ON account.customer_id=customer.customer_id;

Now the result looks like this:

Full outer join result

Notice how the last five rows have account attributes populated with NULLs. This is because these customers do not have records in the account table. Notice also how customers 50, 51, 52, and 53 have first or last names and other attributes from the customer table populated with NULLs. This is because they don't exist in the customer table. Here, customer_id in the result table is never NULL because we defined customer_id with a CASE WHEN statement:

CASE WHEN customer.customer_id IS NULL
                 THEN account.customer_id 
                 ELSE customer.customer_id END customer_id

This actually means that customer_id in the result table is a combination of account.customer_id and customer.customer_id (i.e. when one is NULL, use the other one). We could also display both columns in the output, but this CASE WHEN statement is more convenient.

It’s okay if you are confused by all the different SQL JOINs and what they do. Just stick with it. I’d recommend that you look at our SQL JOIN Cheat Sheet. Keep it near you; it is very useful when coding. It’s also helpful to review our article on practicing SQL JOINs. The more you learn and practice, the clearer SQL JOINs will be.

Up Next: Practice SQL JOINs

In this article, we’ve introduced different SQL JOIN types. Inner, left, right, and full joins all return different results. Now you need to put that knowledge into action! At LearnSQL.com, you can find more examples to practice on. Our interactive SQL course for beginners and SQL JOINs course cover JOIN topics, so I encourage you to try them out.