INNER JOIN vs. OUTER JOIN: What’s the Difference?

Deciding when to use INNER JOIN vs. OUTER JOIN is often challenging for beginners. In this article, you will find explanations and examples that will help you better understand the difference between these two joins.

In SQL, JOINs allow you to combine data from different tables; INNER JOIN and OUTER JOIN are simply types of JOIN statements. Understanding their differences is critical if you work with relational databases. It is also crucial if you’re going for an SQL job interview: the difference between INNER JOIN and OUTER JOIN is among the most frequently asked SQL interview questions.

To review your knowledge of JOINs, I recommend our interactive SQL JOINs course. You will find 93 practical exercises covering all the different use cases of INNER JOIN vs. OUTER JOIN. In addition, you will learn how to filter data correctly with different JOIN variants and how to combine tables with non-key columns.

What Is a JOIN in SQL?

Let’s first start with reviewing a basic SQL JOIN.

Imagine you have a university database with the tables student and lecturer. In the student table, we have the optional field advisor_id that stores the ID of the lecturer who is that student’s thesis advisor. A student may not have an advisor if they are not writing their thesis yet.

To display the names of students and their advisors, we use the JOIN statement:

SELECT
  student.full_name AS student_name,
  lecturer.full_name AS advisor_name
FROM student
JOIN lecturer
ON student.advisor_id = lecturer.id;

The resulting data could look like this:

The JOIN statement comes after the FROM clause: you put the name of the first table after FROM, then the JOIN keyword, then the name of the other table, followed by the ON keyword and the JOIN condition. In our case, the JOIN condition is student.advisor_id = lecturer.id.

This query returns rows where the advisor_id in the student table matches the id in the lecturer table. Basically, it returns the names of the students together with the names of their advisors.

For future reference on all types of JOINs, we recommend bookmarking or printing our SQL JOIN Cheat Sheet.

What Is an INNER JOIN?

When you use the JOIN keyword in SQL, you’re actually doing an INNER JOIN. An INNER JOIN returns all combinations of rows from the two tables that satisfy the ON condition. In our example, it returned the names of students and their advisors.

However, an INNER JOIN is not enough if you want to include rows that don’t have a match in the other table. For example, you may want to display the names of all students, even those that don’t have an advisor yet. Or perhaps you need to include the names of all lecturers, even those who are not advising any students. This is when OUTER JOIN comes into play.

What Is an OUTER JOIN?

An OUTER JOIN is the type of JOIN that returns the matching rows from the two tables plus all the unmatched rows from one (or sometimes both) of the tables. OUTER JOIN has three variants: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. Let’s examine each of them.

LEFT OUTER JOIN

LEFT OUTER JOIN (or simply LEFT JOIN) returns all the rows of the left table (the table to the left of the JOIN statement, i.e. immediately after FROM) with matching data from the right table (the table to the right of (i.e. immediately after) the JOIN statement). If there’s no match, then the columns coming from the right table are filled with NULL.

Using the student and lecturer tables as an example, we can use LEFT JOIN to get a list of all students with their advisor data. We want to include all students, even those who don’t have an advisor yet. The query would look like this:

SELECT
  student.full_name AS student_name,
  lecturer.full_name AS advisor_name
FROM student
LEFT JOIN lecturer
ON student.advisor_id = lecturer.id;

The results are:

In the results, we can see a row that was not included previously. The student Diana Prince has no advisor, so she was not included in the result of the INNER JOIN query. However, with a LEFT JOIN, she is included. LEFT JOIN includes all rows from the left table, even those who have no corresponding row in the right table. The columns that come from the lecturer table are shown as NULL.

Read this article on LEFT OUTER JOINs in SQL if you need more information.

RIGHT OUTER JOIN

RIGHT OUTER JOIN (or simply RIGHT JOIN) returns all the rows of the right table (the second table) with matching data from the left table (the first table). If there’s no match, then the columns coming from the left table are filled with nulls.

Suppose we want to list lecturers with the data of the students they are advising. We want to include all lecturers, even those who are not advising any students. So we would write a query like this:

SELECT
  student.full_name AS student_name,
  lecturer.full_name AS lecturer_name
FROM student
RIGHT JOIN lecturer
ON student.advisor_id = lecturer.id;

The result of this query is:

Using RIGHT JOIN in the query allows us to get a list of all lecturers, regardless of whether they are assigned to students or not. If a lecturer is assigned to a student, they are listed with the student's name. If a lecturer is not assigned to any students, the student data in the result set is NULL.

Check this complete guide to SQL joins to get all the resources you need to fully understand this subject.

FULL OUTER JOIN

FULL OUTER JOIN (or simply FULL JOIN) allows us to get all the data from both tables, whether or not there is a match between their rows. In our example, the use of FULL JOIN between student and lecturer returns all students and all lecturers. The query would look like this:

SELECT
  student.full_name AS student_name,
  lecturer.full_name AS advisor_name
FROM student
FULL JOIN lecturer
ON student.advisor_id = lecturer.id;

And the results would be:

In the result, you can see all students and all lecturers. If a student doesn’t have an advisor, the advisor data is NULL. If a lecturer is not assigned to any students, the corresponding student data is NULL.

Find more examples and put your knowledge to the test with these 12 SQL JOIN practice questions.

There are some relational database management systems (RDBMSs), such as MySQL and MariaDB, that do not support FULL JOIN. You can get the same result in these DBMSs, but the query is a bit longer.

The way to make a FULL JOIN in the versions of SQL that do not support that command is to use UNION between the INNER JOIN, the LEFT JOIN, and the RIGHT JOIN:

SELECT
  student.full_name AS student_name,
  lecturer.full_name AS advisor_name
FROM student
LEFT JOIN lecturer
ON student.advisor_id = lecturer.id

UNION
SELECT
  student.full_name AS student_name,
  lecturer.full_name AS advisor_name
FROM student
JOIN lecturer
ON student.advisor_id = lecturer.id

SELECT
  student.full_name AS student_name,
  lecturer.full_name AS advisor_name
FROM student
RIGHT JOIN lecturer
ON student.advisor_id = lecturer.id;

In the above script, the union of the three forms of JOIN – LEFT JOIN, INNER JOIN, and RIGHT JOIN – results in the same data as FULL JOIN in the RDBMS that support it.

When you feel you’ve got the hang of SQL JOINs, read these SQL JOIN interview questions and answers to check if you’re ready for that job interview.

Summary of INNER JOIN vs. OUTER JOIN

We can synthesize what we’ve learned about INNER JOIN  and OUTER JOIN in the following table:

Check out this list of seven SQL JOIN examples with explanations for more details.

Where to Learn More About INNER JOIN and OUTER JOIN

Now that you know the differences between INNER JOIN vs. OUTER JOIN, you can write accurate queries that generate reliable and precise information. With practice, you’ll be able to tackle any SQL interview question about the differences between INNER JOIN and OUTER JOIN.

Before you go, I recommend our SQL JOINs course if you want to deepen your knowledge. By the end of the course, you’ll know the different ways to get combined data from multiple tables. And you’ll understand how to choose the best option for each scenario.

Now it's your turn to start getting information about the differences between INNER JOIN and OUTER JOIN. Happy learning!