What Are the Different SQL JOIN Types?

You just can’t understate the importance of the JOIN when learning SQL. Not only is it one of the most fundamental operations in relational databases, but it is also very versatile with many different types. Although SQL JOIN may be intimidating at first, it becomes a very handy instrument in your arsenal once you understand it. Let’s explore all the SQL JOIN types!

We can safely say the JOIN operation is one of the most powerful features of SQL. It is the source of envy for all non-relational DBMSs (database management systems) – it’s a very simple concept yet widely applicable in cases that join two sets of data.

In a nutshell, the JOIN is an operation in which tables are compared to each other, row by row. This comparison lets you output columns from any table involved in the JOIN.

A great place for exercises after reading this article is our interactive SQL JOINs course. It offers over 90 hands-on exercises on different kinds of joins discussed here.

Now, let’s create 2 tables for us to explore and master the art of JOIN. Our first table, employees, consists of 5 columns:

• id - The unique ID of the employee.
• name - The employee’s name.
• specialization - The employee’s specialization in the company.
• coach_id - The ID of the employee’s coach/mentor. This is another employee of a company.
• project_id - The ID of the current project in which the employee is involved.

The second table is projects. It contains 3 columns:

• id - The unique ID of the project.
• name - The name of the project.
• is_external - A Boolean value that represents whether the project is external or internal.

Notice there is a logical connection between the two tables. The project_id in employees corresponds to the id from projects. The corresponding values across the tables are color-coded for a better presentation.

Now that we have the tables, let’s get to the point!

INNER JOIN (a.k.a. JOIN)

The first of the SQL JOIN types is the INNER JOIN. When you type “JOIN” in your SQL code, it uses the INNER JOIN. So, while it doesn’t hurt, you don’t need to write INNER in your queries.

An ON keyword follows all JOINs in SQL. It indicates the start of the connection condition. INNER JOINs, like all other JOINs, outputs only the rows that satisfy the connection condition.

Before going further, note the JOIN operator is not the only way to join data from two tables. You can also use WHERE. Interested in learning more about it? We have just the right article for you!

But let’s put the theory aside for a moment and get down to coding. Let’s use our two tables and an INNER JOIN to find out which employee works on which project:

SELECT
 employees.name as employee,
 specialization,
 projects.name as project
FROM employees
JOIN projects
 ON employees.project_id = projects.id

Before looking at the result of this query, let’s analyze the query itself. As you can see, we join the two tables together using JOIN. After JOIN, we specify the operator ON and the condition of the join.

So, we have joined the tables based on the condition that project_id from the table employees is equal to the id from the table projects. Let’s look at the result of the query:

We see Olli Woodward and Pete Nolan have a project “Website UI/UX Design”. Their project_id is equal to 1, which is the ID of this project in the table projects. The same goes for other rows in the result.

However, we don’t see Mary Shelton here. Her project_id is NULL, and there is no project with the id equal to NULL. Similarly, the project “Research & Development” has the ID 2, but no employee has the project_id equal to 2.

Have we got you interested in the INNER JOIN and wanting to know more of its intricacies? Take a look at our article specifically about the INNER JOIN!

LEFT OUTER JOIN

The LEFT OUTER JOIN is one of the outer SQL JOIN types. The OUTER keyword is optional and is often omitted, thus leaving us with just the LEFT JOIN operator.

The general difference between the OUTER JOIN and the INNER JOIN is that the OUTER JOIN includes not just the rows that satisfy the connection condition but also the rows that do not satisfy the condition from one (or more) of the tables.

Getting back to the LEFT OUTER JOIN specifically, it includes the rows that satisfy the connection condition (just like the INNER JOIN) and all other rows from the left-hand (or the first) table. Let’s use our earlier example, changing JOIN to LEFT JOIN to see the difference:

SELECT
 employees.name as employee,
 specialization,
 projects.name as project
FROM employees
LEFT JOIN projects
 ON employees.project_id = projects.id

And here is the result of the query:

As you can see, one more row has been added: Mary Shelton with the NULL project. This particular row does not satisfy the condition employees.project_id = projects.id, as there are no projects with a NULL ID. But this row exists in the employees table, so it is added to the result. The name of the project for Mary Shelton is a NULL value for the same reason since there are no IDs of NULL among the projects.

Now you get the main idea of the LEFT OUTER JOIN. That said, you can always read our article on LEFT OUTER JOIN to learn more about it or see more examples of it!

INNER and LEFT OUTER JOINs are the most common types of JOIN in SQL. So, be sure to practice it thoroughly with our SQL JOINs course. Read about the course here.

RIGHT OUTER JOIN

The RIGHT OUTER JOIN may be viewed as a twin brother of the LEFT OUTER JOIN. The only difference between these two SQL JOIN types is the table from which the records are taken regardless of the JOIN condition. In the LEFT OUTER JOIN, that is the first or left-hand table; in the RIGHT OUTER JOIN, it is the second or right-hand table.

Let’s use the previous example, but now change LEFT JOIN to RIGHT JOIN:

SELECT
 employees.name as employee,
 specialization,
 projects.name as project
FROM employees
RIGHT JOIN projects
 ON employees.project_id = projects.id

Now, let’s analyze the result of the query:

As we can see, Mary Shelton’s record is gone now. It doesn’t satisfy the ON condition, nor does it belong to the right-hand table. So, it is not included in the result here.

On the other hand, we now get the “Research & Development” project here. It does not satisfy the JOIN condition, but it does exist in the right-hand table, so it is included in the result. Notice this row has NULLs in employee and specialization columns. This is because no record in the employees table has project_id equal to 2, the ID of this project. Therefore, a NULL is returned instead.

FULL OUTER JOIN

The FULL OUTER JOIN, or simply FULL JOIN, is the last type of the OUTER JOIN. In a way, it combines both LEFT and RIGHT OUTER JOINs.

While it includes records that satisfy the JOIN condition, it also includes rows from both tables that do not. That is, a FULL JOIN returns all rows from both tables. If a row from one table does not have a corresponding record from the other table, then the missing data is filled with NULLs.

Let’s see an example of the FULL OUTER JOIN:

SELECT
 employees.name as employee,
 specialization,
 projects.name as project
FROM employees
FULL JOIN projects
 ON employees.project_id = projects.id

Here is the result:

As you can see, here we have all rows from both tables. The rows that satisfy the condition have the corresponding data from both tables. For the rows that do not, the columns from the other table are filled with NULLs.

If you want an accessible, brief description of the FULL OUTER JOIN as well as of other SQL JOIN types, use our SQL JOIN Cheat Sheet.

CROSS JOIN

This SQL JOIN type is quite different from the ones mentioned so far in this article. Not only is its purpose different, but it also has a slightly different syntax.

When used in a query, a CROSS JOIN returns a Cartesian product – all possible combinations of the rows from both tables. All records from one table are automatically joined with all records from the other.

Also, there is no ON condition. For this JOIN in a query, simply use the CROSS JOIN operator without following with the ON keyword.

Let’s look at how it all works in an example:

SELECT
 employees.name as employee,
 projects.name as project
FROM employees
CROSS JOIN projects

Note we select two columns, the name of the employee and the name of the project, followed by CROSS JOIN. Let’s look at the result:

Note the number of rows it returns. It is pretty easy to calculate this: the number of rows in the first table * the number of rows in the second table = the number of resulting rows. Thus, every one of the 6 employees is assigned to each of the 4 projects.

Do you still have some lingering questions about the CROSS JOIN? Or maybe you just want to learn more about it. Check out our Illustrated Guide to the SQL CROSS JOIN!

Practice All the SQL JOIN Types!

Now you know about different types of JOIN in SQL. This is a great opportunity to deepen this new knowledge or simply review what you already know. Either way, I recommend our great interactive SQL JOINs course. With over 90 hands-on exercises, it is an opportunity to practice this fundamental aspect of SQL!