Inner Join vs Outer Join
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.
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
- What is Inner Join?
- Example of Inner Join
- What is Outer Join?
- Example of Outer Join
- Conclusion
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 |
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
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:
The syntax of Inner Join is:
SELECT column_name(s)FROM table1INNER JOIN table2ON table1.column_name = table2.column_name;
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;
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:
Here’s the syntax of Left Outer Join:
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
Here’s the syntax of Right Outer Join:
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Here’s the syntax of Full Join:
SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;
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(+);
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
Anshuman Singh is an accomplished content writer with over three years of experience specializing in cybersecurity, cloud computing, networking, and software testing. Known for his clear, concise, and informative wr... Read Full Bio
Top Picks & New Arrivals
