Inner Join Vs Outer Join: Exact Difference With Examples

Inner Join Vs Outer Join: Get Ready to Explore the Exact Differences Between Inner and Outer Join

Before exploring the differences between Inner Join Vs Outer Join, let us first see what is a SQL JOIN?

A join clause is used to combine records or to manipulate the records from two or more tables through a join condition. The join condition indicates how columns from each table are matched against each other.

Join is based on a related column between these tables. A most common example is the join between two tables through the primary key column and foreign key column.

INNER JOIN VS OUTER JOIN

Suppose, we have got a table which contains employee Salary and there is another table which contains employee details.

In this case, there will be a common column like employee ID which will join these two tables. This Employee ID column would be the primary key of the employee details tables and foreign key in the employee salary table.

It is very important to have a common key between the two entities. You can think of a table as an entity and the key as a common link between the two tables which is used for join operation.

Basically, there are two types of Join in SQL i.e. Inner Join and Outer Join. Outer join is further subdivided into three types i.e. Left Outer Join, Right Outer Join, and Full Outer Join.

In this article, we will see the difference between Inner Join and Outer Join in detail. We will keep the Cross Joins and Unequal Joins out of the scope of this article.

What is Inner Join?

An Inner Join returns only the rows that have matching values in both the tables (we are considering here the join is done between the two tables).

What is Outer Join?

The Outer Join includes the matching rows as well as some of the non-matching rows between the two tables. An Outer join basically differs from the Inner join in how it handles the false match condition.

There are 3 types of Outer Join:

  • Left Outer Join: Returns all the rows from the LEFT table and matching records between both the tables.
  • Right Outer Join: Returns all the rows from the RIGHT table and matching records between both the tables.
  • Full Outer Join: It combines the result of the Left Outer Join and Right Outer Join.

Difference between Inner and Outer Join

Difference between INNER JOIN and OUTER JOIN

[image source]

As shown in the above diagram, there are two entities i.e. table 1 and table 2 and both the tables share some common data.

An Inner Join will return the common area between these tables (the green shaded area in the diagram above) i.e. all the records that are common between table 1 and table 2.

A Left Outer Join will return all the rows from table 1 and only those rows from table 2 which are common to table 1 as well. A Right Outer Join will do just the opposite. It will give all the records from table 2 and only the corresponding matching records from table 1.

Furthermore, a Full Outer Join will give us all the records from table 1 and table 2.

Let us start with an example to make this clearer.

Suppose we have two tables: EmpDetails and EmpSalary.

EmpDetails Table:

EmployeeIDEmployeeName
1John
2Samantha
3Hakuna
4Silky
5Ram
6Arpit
7Lily
8Sita
9Farah
10Jerry

EmpSalary Table:

EmployeeIDEmployeeNameEmployeeSalary
1John50000
2Samantha120000
3Hakuna75000
4Silky25000
5Ram150000
6Arpit80000
11Rose90000
12Sakshi45000
13Jack250000

Let us do an Inner Join on these two tables and observe the result:

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary
FROM EmpDetails INNER JOIN EmpSalary
ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

EmployeeIDEmployeeNameEmployeeSalary
1John50000
2Samantha120000
3Hakuna75000
4Silky25000
5Ram150000
6Arpit80000

In the above result set, you can see that Inner Join has returned the first 6 records that were present in both EmpDetails and EmpSalary having a matching key i.e. EmployeeID. Hence, if A and B are two entities, the Inner Join will return the result set that will be equal to ‘Records in A and B’, based on the matching key.

Let us now see what a Left Outer Join will do.

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary
FROM EmpDetails LEFT JOIN EmpSalary
ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

EmployeeIDEmployeeNameEmployeeSalary
1John50000
2Samantha120000
3Hakuna75000
4Silky25000
5Ram150000
6Arpit80000
7LilyNULL
8SitaNULL
9FarahNULL
10JerryNULL

In the above result set, you can see that the left outer join has returned all the 10 records from the LEFT table i.e. EmpDetails table and as the first 6 records are matching, it has returned the employee salary for these matching records.

As the rest of the records do not have a matching key in the RIGHT table, i.e. EmpSalary table, it has returned NULL corresponding to those. Since, Lily, Sita, Farah, and Jerry do not have a matching employee ID in the EmpSalary table, their Salary is showing up as NULL in the result set.

So, if A and B are two entities, then left outer join will return the result set that will be equal to ‘Records in A NOT B’, based on the matching key.

Now let us observe what the Right Outer Join does.

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary
FROM EmpDetails RIGHT join EmpSalary
ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

EmployeeIDEmployeeNameEmployeeSalary
1John50000
2Samantha120000
3Hakuna75000
4Silky25000
5Ram150000
6Arpit80000
NULLNULL90000
NULLNULL250000
NULLNULL250000

In the above result set, you can see that the Right Outer Join has done just the opposite of the left join. It has returned all the salaries from the right table i.e. EmpSalary table.

But, as Rose, Sakshi, and Jack do not have a matching employee ID in the left table i.e. EmpDetails table, we have got their Employee ID and EmployeeName as NULL from the left table.

So, if A and B are two entities, then the right outer join will return the result set that will be equal to ‘Records in B NOT A’, based on the matching key.

Let us also see what will be the result set if we are doing a select operation on all the columns in both the tables.

Query:

SELECT *
FROM EmpDetails RIGHT JOIN EmpSalary
ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

EmployeeIDEmployeeNameEmployeeIDEmployeeNameEmployeeSalary
1John1John50000
2Samantha2Samantha120000
3Hakuna3Hakuna75000
4Silky4Silky25000
5Ram5Ram150000
6Arpit6Arpit80000
NULLNULL11Rose90000
NULLNULL12Sakshi250000
NULLNULL13Jack250000

Now, let us move into the Full Join.

A full outer join is done when we want all the data from both the tables irrespective of if there is a match or not. Hence, if I want all the employees even if I do not find a matching key, I will run a query as shown below.

Query:

SELECT *
FROM EmpDetails FULL JOIN EmpSalary
ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

EmployeeIDEmployeeNameEmployeeIDEmployeeNameEmployeeSalary
1John1John50000
2Samantha2Samantha120000
3Hakuna3Hakuna75000
4Silky4Silky25000
5Ram5Ram150000
6Arpit6Arpit80000
7LilyNULLNULLNULL
8SitaNULLNULLNULL
9FarahNULLNULLNULL
10JerryNULLNULLNULL
NULLNULL11Rose90000
NULLNULL12Sakshi250000
NULLNULL13Jack250000

You can see in the above result set that as the first six records are matching in both the tables, we have got all the data without any NULL. The next four records exist in the left table but not in the right table, thus the corresponding data in the right table is NULL.

The last three records exist in the right table and not in the left table, hence we have NULL in the corresponding data from the left table. So, if A and B are two entities, the full outer join will return the result set that will be equal to ‘Records in A AND B’, irrespective of the matching key.

Theoretically, it is a combination of Left Join and Right Join.

Performance

Let us compare an Inner Join against a Left Outer Join in the SQL server. Talking about the speed of operation, a left outer JOIN is obviously not faster than an inner join.

As per the definition, an outer join, be it the left or right, it has to perform all the work of an inner join along with the additional work null- extending the results. An outer join is expected to return a greater number of records which further increases its total execution time just because of the larger result set.

Thus, an outer join is slower than an inner join.

Moreover, there can be some specific situations where the Left join will be faster than an Inner join, but we can’t go on for replacing them with each other as a left outer join is not functionally equivalent to an inner join.

Let us discuss an instance where the Left Join might be faster than the Inner Join. If the tables involved in the join operation are too small, say they have less than 10 records and the tables do not possess sufficient indexes to cover the query, in that case, the Left Join is generally faster than Inner Join.

Let us create the two below tables and do an INNER JOIN and a LEFT OUTER JOIN between them as an Example:

CREATE TABLE #Table1
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Table1 (ID, Name) VALUES (1, 'A')
INSERT #Table1 (ID, Name) VALUES (2, 'B')
INSERT #Table1 (ID, Name) VALUES (3, 'C')
INSERT #Table1 (ID, Name) VALUES (4, 'D')
INSERT #Table1 (ID, Name) VALUES (5, 'E')

CREATE TABLE #Table2
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Table2 (ID, Name) VALUES (1, 'A')
INSERT #Table2 (ID, Name) VALUES (2, 'B')
INSERT #Table2 (ID, Name) VALUES (3, 'C')
INSERT #Table2 (ID, Name) VALUES (4, 'D')
INSERT #Table2 (ID, Name) VALUES (5, 'E')

SELECT *
FROM #Table1 t1
INNER JOIN #Table2 t2
ON t2.Name = t1.Name
 IDNameIDName
11A1A
22B2B
33C3C
44D4D
55E5E
SELECT * 
FROM (SELECT 38 AS bah) AS foo 
JOIN 
(SELECT 35 AS bah) AS bar
ON (55=55);
  IDNameIDName
11A1A
22B2B
33C3C
44D4D
55E5E

As you can see above, both the queries have returned the same result set. In this case, if you view the execution plan of both the queries, then you will find that the inner join has costed more than the outer join. This is because, for an inner join, the SQL server does a hash match whereas it does nested loops for the left join.

A hash match is normally faster than the nested loops. But, in this case, as the number of rows is so small and there is no index to use (as we are doing join on name column), the hash operation has turned out a most expensive inner join query.

However, if you change the matching key in the join query from Name to ID and if there are a large number of rows in the table, then you will find that the inner join will be faster than the left outer join.

MS Access Inner and Outer Join

When you use multiple data sources in MS Access query, then you apply JOINs to control the records that you want to see, depending upon how the data sources are linked with each other.

In an inner join, only the related ones from both the tables are combined in a single result set. This is a default join in Access and the most frequently used one too. If you apply a join but do not explicitly specify what type of Join it is, then the Access assumes that it is an inner join.

In outer joins, all the related data from both the tables are combined correctly, plus all the remaining rows from one table. In full outer joins, all data are combined wherever possible.

Left Join vs Left Outer Join

In SQL server, the keyword outer is optional when you apply left outer join. Thus, it does not make any difference if you either write ‘LEFT OUTER JOIN’ or ‘LEFT JOIN’ as both are going to give you the same result.

A LEFT JOIN B is an equivalent syntax to A LEFT OUTER JOIN B.

Below is the list of equivalent syntaxes in the SQL server:

equivalent syntaxes in SQL server

[image source]

Left Outer Join vs Right Outer Join

We have already seen this difference in this article. You can refer to the Left Outer Join and Right Outer Join queries and result set to see the difference.

The main difference between the Left Join and Right Join lies in the inclusion of non-matched rows. Left outer join includes the unmatched rows from the table which is on the left of the join clause whereas a Right outer join includes the unmatched rows from the table which is on the right of the join clause.

People do ask which is better to use i.e. Left join or Right join? Basically, they are the same type of operations except with their arguments reversed. Hence, when you ask which join to use, you are actually asking whether to write a<b or b>a. It is just a matter of preference.

Generally, people prefer to use Left join in their SQL query. I would suggest that you should stay consistent in the way you in which are writing the query in order to avoid any confusion in interpreting the query.

We have seen all about Inner join and all type of Outer joins so far. Let us quickly summarize the difference between Inner Join and Outer Join.

Difference between Inner Join and Outer Join in Tabular Format

Inner JoinOuter Join
Returns only the rows that have matching values in both the tables.Includes the matching rows as well as some of the non-matching rows between the two tables.
In case there are a large number of rows in the tables and there is an index to use, INNER JOIN is generally faster than OUTER JOIN.Generally, an OUTER JOIN is slower than an INNER JOIN as it needs to return more number of records when compared to INNER JOIN. However, there can be some specific scenarios where OUTER JOIN is faster.
When a match is not found, it does not return anything.When a match is not found, a NULL is placed in the column value returned.
Use INNER JOIN when you want to look up detailed information of any specific column.Use OUTER JOIN when you want to display the list of all the information in the two tables.
INNER JOIN acts like a filter. There must be a match on both the tables for an inner join to return data.They act like data-add ons.
Implicit join notation exists for inner join which enlists tables to be joined in the comma separated manner in the FROM clause.
Example: SELECT * FROM product, category WHERE product.CategoryID = category.CategoryID;
No implicit join notation is there for outer join.
Below is the visualization of an inner join:
visualization of an inner join
Below is the visualization of an outer join
visualization of an outer join

Inner and Outer Join vs Union

At times, we confuse Join and Union and this is also one of the most commonly asked questions in SQL interviews. We have already seen the difference between inner join and outer join . Now, let us see how a JOIN is different from a UNION.

UNION places a line of queries after each other, whereas join creates a cartesian product and subsets it. Thus, UNION and JOIN are completely different operations.

Let us run the below two queries in MySQL and see their result.

UNION Query:

SELECT 28 AS bah
UNION
SELECT 35 AS bah;

Result:

  Bah
128
235

JOIN Query:

SELECT * FROM
(SELECT 38 AS bah) AS foo
JOIN
(SELECT 35 AS bah) AS bar
ON (55=55);

Result:

  fooBar
13835

A UNION operation puts the result of two or more queries into a single result set. This result set holds all the records that are returned through all the queries involved in the UNION. Thus, basically, a UNION is combining the two result sets together.

A join operation fetches data from two or more tables based on the logical relationships between these tables i.e. based on the join condition. In join query, data from one table is used to select records from another table. It lets you link similar data that is present over different tables.

In order to understand it very simply, you can say that a UNION combines rows from two tables whereas a join combines columns from two or more tables. Thus, both are used to combine the data from n tables, but the difference sits in how the data is combined.

Below are the pictorial representations of UNION and JOIN.

UNION and JOIN1

The above is a pictorial representation of a Join Operation depicting that each record in the result set contains columns from both the tables i.e. Table A and Table B. This result is returned based on the join condition applied in the query.

A join is generally the result of denormalization (opposite of normalization) and it uses the foreign key of one table to look up the column values by employing primary key in another table.

UNION and JOIN2

The above is a pictorial representation of a UNION Operation depicting that each record in the result set is a row from either of the two tables. Thus, the result of the UNION has combined the rows from Table A and Table B.

Further reading =>> MySQL UNION explained with examples

Conclusion

In this article, we have seen the major differences between the Inner Join and Outer Join in SQL. We also saw the classification of an Outer Join i.e. Left join, Right join and Full join. We have seen how each of these join types do work and how they vary from each other.

We also did some performance comparison between these join types. We also discussed how a join is different from a union.

Also Read =>> MySQL Join Types

Hope this article would have helped you in clearing your doubts regarding the differences between the various join types. We are sure that this indeed will make you decide which join type to choose from based upon the desired result set.