Inner Join vs Outer Join

Inner Join vs Outer Join

5 mins read610 Views Comment
clickHere
Updated on Oct 3, 2023 12:25 IST

The primary difference between Inner Join vs Outer Join is that Inner Join results in the intersection of two tables, while Outer Join results in the union of two tables.

2022_10_MicrosoftTeams-image-110.jpg

This article will cover Inner Join vs Outer Join in great detail. But before we do so, let’s understand what a SQL join is. So, A SQL join is responsible for combining rows from two or more tables based on a related column between them. Now that we know what a SQL join is, let’s discuss Inner Join vs. Outer Join after going through the topics listed under the table of contents (TOC) we will cover in this article.

You can also explore: What are Constraints in SQL?

Table of Contents (TOC)

Inner Join vs Outer Join

For a simpler and better understanding, let’s go through Inner Join vs Outer Join in a tabular format:

Parameter Inner Join Outer Join
Displays Matching tuples (data) from both the tables All tuples (data) of both the tables
Size of resulting table Small Large
Subtypes No subtypes Three subtypes (Left Outer Join, Right Outer Join, Full Outer Join
Speed Fast Slow
When a match is not found It does not return anything. A NULL is placed in the column value returned.
Acts like a Filter Data-add ons
Implicit join notation exists Yes No

What is Inner Join?

Inner Join definition: Inner Join is a type of SQL Join that retrieves related data from two or more tables in the resulting table.

You can also explore: INNER JOIN in SQL

In simpler terms, the inner join retains only the information that is related to the two joined tables. If you imagine the two tables as an oval, the table formed by an INNER JOIN will be the blue highlighted section below where both ovals overlap:

2022_10_inner.jpg

The syntax of Inner Join is:

 
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Copy code

Example of Inner Join

Consider there are two tables: Table 1 and Table 2.

Here’s the data from Table 1:

ID Name Age Salary
1 Atul 28 5000
2 Kundan 27 4000
3 Aquib 25 3000
4 Anshuman 25 3000
5 Vikram 29 4000

Here’s the data from Table 2:

OID Date Customer_ID Amount
102 2022-17-10 3 1500
100 2022-17-10 3 2000
102 2022-27-10 2 1000
103 2022-28-10 4 500

Let us now join the above tables using the Inner Join as shown:

 
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
Copy code

The resulting table will be something like this:

ID Name Amount Date
3 Aquib 1500 2022-17-10
3 Aquib 1500 2022-17-10
2 Kundan 1000 2022-27-10
4 Anshuman 500 2022-28-10

What is Outer Join?

Outer Join definition: Outer Join is a type of SQL Join that retrieves all the data, related and not related, from two or more tables in the resulting table.

There are three types of Outer Join, such as:

  • Left Outer Join: This type of Outer Join keeps data from the left table (First table) along with the related data from both tables.
  • Right Outer Join: This type of Outer Join keeps data from the right table (Second table) along with the related data from both tables.
  • Full Join: This type of Outer Join is a combination of Left and Right Outer Join; hence it keeps all rows from both tables. The missing data will be filled in with NULL.

Let’s try to understand the difference between Inner Join vs Left Outer Join vs Right Outer Join vs Full Join with help of the figure shown below:

2022_10_inner-1.jpg

Here’s the syntax of Left Outer Join:

 
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Copy code

Here’s the syntax of Right Outer Join:

 
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Copy code

Here’s the syntax of Full Join:

 
SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;
Copy code

You can also explore: How to CREATE TABLE in SQL

Example of Outer Join

Consider there are two tables: Table 1 and Table 2.

Here’s the data from Table 1:

Item_ID Item_Name Company_ID
1 Itemone 16
6 Itemtwo 15
2 Itemthree 15
3 Itemfour 17
4 Itemfive 15
5 Itemsix 18
7 Itemseven

Here’s the data from Table 2:

Comapany_ID Company_Name Comapny_City
18 Naukricom Dehli
15 Naukrilearning Banglore
16 Ambitionbox Chennai
17 Jeevansathi Mumbai
19 99acres Chandigarh

Let us now join the above tables using the Outer Join as shown:

 
SELECT company.company_name,company.company_id,
foods.company_id,foods.item_name,foods.item_unit
FROM company, foods
WHERE company.company_id = foods.company_id(+);
Copy code

The resulting table will be something like this:

Company_Name Comapany_ID Comapany_ID Item_Name
Ambitionbox 16 16 Itemone
Naukrilearning 15 15 Itemtwo
Naukrilearning 15 15 Itemthree
Jeevansathi 17 17 Itemfour
Naukrilearning 15 15 Itemfive
Naukricom 18 18 Itemsix
99acres 19

You can also explore: What is the Difference Between SQL and MySQL?

Conclusion

In this article, we discussed the major differences between Inner Join vs Outer Join in SQL. While both can combine tables, they are not the same. The tables produced by an inner join are smaller in size than those produced by an outer join. So, depending on the user’s needs, they can use any of the tables.

FAQs

What is the main difference between Inner Join vs Outer Join?

The main difference between Inner Join vs Outer Join is that Inner Join results in the intersection of two tables, while Outer Join results in the union of two tables.

In regards to Inner Join vs Outer Join, what is Inner Join?

In regards to Inner Join vs Outer Join, Inner Join is a type of SQL Join that retrieves related data from two or more tables in the resulting table.

In regards to Inner Join vs Outer Join, what is Outer Join?

In regards to Inner Join vs Outer Join, Outer Join is a type of SQL Join that retrieves all the data, related and not related, from two or more tables in the resulting table.

In regards to Inner Join vs Outer Join, what is Left Outer Join?

In regards to Inner Join vs Outer Join, Left Outer Join keeps data from the left table (First table) along with the related data from both tables.

In regards to Inner Join vs Outer Join, what is Right Outer Join?

In regards to Inner Join vs Outer Join, Right Outer Join keeps data from the right table (Second table) along with the related data from both tables.

In regards to Inner Join vs Outer Join, what is Full Join?

In regards to Inner Join vs Outer Join, Full Outer Join is a combination of Left and Right Outer Join; hence it keeps all rows from both tables

About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio