Difference Between “INNER JOIN” and “OUTER JOIN”

Are you working with SQL and struggling to understand the differences between INNER JOIN and OUTER JOIN? These two types of joins are among the most commonly used tools for combining tables in SQL queries. Whether you're analyzing student data or managing customer records, knowing when to use INNER JOIN and OUTER JOIN can make your queries faster, more efficient, and more insightful.

What is a JOIN in SQL?

Joins in SQL are used to combine rows from multiple tables on a specific condition which is a relation between the columns of two tables. Joins allow you to link data together that’s spread across different tables, making it easier to analyze and retrieve meaningful information. Let us consider the two tables student and location and see what the differences would look like by combing tables using different joins.

Table1: students

Table 2: location

Types of JOINS

There are several types of joins in SQL, but today we’re going to focus on two main types: INNER JOIN and OUTER JOIN.

1. Inner Join 

The INNER JOIN keyword selects records that have matching values in both tables. If there’s no match, the row is excluded from the result set. It's a great option when you only need data that exists in both tables.

 Venn diagram representation of the Inner Join:

Example: Let's say we have two tables, one for students and another for locations. We want to see a list of students and their corresponding locations, but only for students who have a location listed.

Query:

SELECT student.student_name, location.student_location
FROM student
INNER JOIN location
ON student.student_id = location.student_id;

Output

Explanation: This will return only the students who have a matching location in the location table. If a student doesn't have a location listed, they won’t appear in the results.

2. Outer Join

OUTER JOIN, on the other hand, is more flexible. It returns all rows from one table and matches data from the other table where possible. If there’s no match, it will still return the row but fill in the missing data with NULL. There are three main types of OUTER JOIN:

1. LEFT JOIN (Left Outer Join)

In left join, we consider the left table completely and the matched attributes (based on condition) in the right table along with the unmatched attributes of the left table. If there’s no match, it returns NULL values for the right table’s columns.

Venn diagram representation of the Left Join:

Example Scenario: We want to see a list of all students and their locations, including students who do not have a location assigned.

SELECT student.student_name, location.student_location
FROM student
LEFT JOIN location
ON student.student_id = location.student_id;

Output

Explanation: This will return only the students who have a matching location in the location table. If a student doesn't have a location listed, they won’t appear in the results.

2. RIGHT JOIN (Right Outer Join)

The RIGHT JOIN is the opposite of LEFT JOIN. It returns all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for the left table’s columns.

 Venn diagram representation of the Right Join

Query:

SELECT student.student_name, location.student_location
FROM student
RIGHT JOIN location
ON student.student_id = location.student_id;

Output

3. FULL JOIN (Full Outer Join)

It is the union of both left join and right join where all the columns of the left table and the right table are considered where the unmatched or unfound attributes of the left table or right table will be placed with NULL in the resultant table.

Venn diagram representation of the full Join:

Query:

SELECT student.student_name, location.student_location
FROM student
FULL JOIN location
ON student.student_id = location.student_id;

Output

Key Differences Between INNER JOIN and OUTER JOIN

Here is a quick comparison to help us understand when to use INNER JOIN vs. OUTER JOIN:

Conclusion

Joins are fundamental to SQL, providing the capability to merge data from different tables in various ways. INNER JOIN is used to retrieve only the matched records between two tables, whereas OUTER JOIN retrieves matched records along with unmatched records from one or both tables, filling in NULL values where necessary. Now that we understand the differences between INNER JOIN and OUTER JOIN, it's time to practice!