INNER JOIN Vs OUTER JOIN in SQL
FREE
Inner join in SQL majorly focuses on returning the common rows and records between two tables. It will search for the matching and overlapping data in the tables and their combined result will be returned in the new output table. On the other side, the outer join is used to return what an inner join would return plus other rows for which no corresponding match is found in the other table. Let's consider that we have one table having the product names and product prices. The other table has the product names and product quantities in the store. The inner join on these two tables will return all those products whose quantities are present in the store along with their price information. The outer join will return all the matching rows as in the inner join, along with the extra rows for which no match is found.
As we can see from the above image, the inner join is the combination and the intersection of the two tables A and B. Whereas, whereas the outer join is the union of the two tables. We will take a further look in detail at joins, inner joins, outer joins, and inner join vs outer join in SQL in the upcoming sections of this article.
INNER JOIN Vs OUTER JOIN in SQL
| INNER JOIN | OUTER JOIN |
|---|---|
| INNER JOIN returns the common and the matching records between the tables. | OUTER JOIN returns all the records from the database tables. |
| Matching records are returned in an INNER JOIN based on common fields or columns. | Because all records are returned, the OUTER JOIN does not require a common column ID. |
| There is no inner join variation. | An outer join is either a left join, a right join, or a full join (cross join). |
| Inner joins are recommended when related data inputs are required. | Outer joins are recommended if you do not need related data entries. |
| There is no output of those entries which are having no matching entries with another table. | If entries of one table do not have any matching entries with another table, then you will get null values. |
| SQL inner joins are slower. | Outer joins are faster as compared to inner joins. |
| To apply an inner join, you must apply a constraint. | There are no absolute requirements for an outer join query. |
| Lack of performance is observed because of the slow speed of the inner join. | Better performance is observed in outer join as compared to inner join in SQL. |
| If the rows in table A do not have any matching entries in table B, then those entries are not considered for output in the inner join. | If the rows in table A do not have any matching entries in table B, then the output will return null values. |
| An inner join of tables A and B yields the result of the intersection of A and B. | An outer join of tables A and B gives the results of A union B. |
| An inner join is treated as a simple join that provides the result directly. | An outer join is a left, right, or full complex join, and here results are not provided directly. |
Difference Between INNER JOIN and OUTER JOIN in Terms of Performance and Reliability
| INNER JOIN | OUTER JOIN |
|---|---|
| Let's see inner join vs outer join in SQL in terms of performance, inner join is less efficient as compared to outer join as it takes time to join common records between two tables. | The outer join is faster than the inner join because suppose, if the database consists of about 20 tables, the query takes 43 seconds to return the first row after a join operation, and another 50 seconds for the last row if the join has not failed. This can add up quickly, especially if you have more joins. This is also because we need to join two tables to get information about the records they contain. |
| Inner join returns less records according to the condition and hence data loss can be there and a well-crafted inner join can efficiently return the required information, even for complex joins involving around hundreds of tables. That's a good compromise. | Full outer join returns both tables. If you know that a table contains data that doesn't belong in any other table, you can easily access the secondary data using an outer join. |
| Inner join is slower in performance as compared to outer join. | Outer joins are much faster and efficient in performance as compared to inner joins. |
What is a JOIN in SQL?
Joins in SQL are used to combine data and rows from two or more tables based on a common column or a field. There are two main types of joins in SQL : inner joins and outer joins.
Inner joins return common information between tables and an outer join returns information in the resulting table that the inner join returns along with the information that is not common with the other table.
What is INNER JOIN?
The inner join in SQL will return the common or the matching records between the tables in the resulting table. It can be represented as:
That green area in the above diagram is the common records from the two tables, which will be resulted in an output.
The syntax for inner join in SQL is:
OR
Let's suppose we have two tables, one is storing the student roll number and student name, and the other is storing the student roll number and the student marks. In this example, the roll number field is common in both tables and for the result, we need to consider the data in both tables (name and marks) based on a common field (in our case its roll number). So, we will apply the inner join on both tables and will return the result in the output table.
Firstly, let's check the data in both tables by running the SELECT query:
The output of the above table would be:
| roll_number | name |
|---|---|
| 1 | Meenakshi |
| 2 | Manjili |
| 3 | Sheetal |
| 4 | Apoorva |
| 5 | Kitto |
| 6 | Nitin |
| 7 | Soumya |
The output of the above table would be:
| roll_number | marks |
|---|---|
| 1 | 89 |
| 3 | 74 |
| 4 | 100 |
| 6 | 29 |
Now, let's apply inner join to both tables:
The output to the above join query is:
| name | marks |
|---|---|
| Meenakshi | 89 |
| Sheetal | 74 |
| Apoorva | 100 |
| Nitin | 29 |
The table includes the names of only those students who are common in both tables.
We can see the above image to understand how the inner join is happening between tables. Basically, we are selecting all the rows from both the tables which have the common roll number field in them.
How to Use INNER JOIN in SQL?
The INNER JOIN works by selecting records that have matching values in both tables. For the records that don't have matching common values, then no record will be shown in the output table.
If there are two tables then we can apply inner join to them using:
If you want to apply the inner join on three tables, then you can use inner join like:
What is OUTER JOIN?
An outer join returns a set of records (or rows) that includes those returned by the inner join, along with other rows for which no corresponding match was found in the other table.
There are three types of outer joins:
- Left outer join (or left join)
- Right outer join (or right join)
- Full Outer Join (or Full Join)
Outer joins are used when you want to return all data, not just the interrelated data. The next few sections of this article will describe each of the outer joins.
Left outer join
The left outer join is one of the types of outer join which returns all the records from the left table and only the matching and related records from the right table. The left join or the left outer join is the combination of the result of the inner join plus all the records of the left table.
The syntax for the left outer join is as shown below:
Let's suppose we have two tables, one storing the student roll number and student name, and the other storing the student roll number and the student's hometown name. In this example, the roll number field is common in both tables and for the result, we need to consider the data in both tables (name and city) based on a common field (in our case its roll number). We will apply the left join on both tables and see what the result we get in the output table is.
Firstly, let's check the data in both tables by running the SELECT query:
The output of the above table would be:
| roll_number | name |
|---|---|
| 1 | Meenakshi |
| 2 | Manjili |
| 3 | Sheetal |
| 4 | Apoorva |
| 5 | Kitto |
| 6 | Nitin |
| 7 | Soumya |
The output of the above table would be:
| roll_number | city |
|---|---|
| 1 | Ajmer |
| 3 | Bangalore |
| 5 | Udaipur |
| 6 | Chittor |
| 10 | Raipur |
Now, let's apply the left outer join:
The output to the above join query is:
| name | city |
|---|---|
| Meenakshi | Ajmer |
| Manjili | NULL |
| Sheetal | Bangalore |
| Apoorva | NULL |
| Kitto | Udaipur |
| Nitin | Chittor |
| Soumya | NULL |
As we can see from the output above, matching results are returned, and all the specified records are returned from the left table even if they are not matching.
We can see the above image to understand how the left outer join is happening between tables. All the records from the left table and all the common rows from both tables are selected.
Right outer join
Right join is another type of outer join, which returns all the records from the right table and only the matching records from the left table. The right join or the right outer join is the combination of the result of the inner join plus all the records of the right table.
The syntax for the right outer join is as shown below:
Let's take the same example and apply the right outer join to see the results in the resultant output table.
The output to the above right join query is:
| name | city |
|---|---|
| Meenakshi | Ajmer |
| Sheetal | Bangalore |
| kitto | Udaipur |
| Nitin | Chittor |
| NULL | Raipur |
As we can see from the output above, matching results are returned, and all the records from the right table are returned.
We can see the above image to understand how the right outer join is happening between tables. All the records from the right table and all the common rows from both tables are selected.
Full Join
The full outer join returns all the records from both tables when a match is in either table. It is the combination of the left outer join and the right outer join.
It is also known as cross join. Basically, a combination of two tables is provided in this join.
The syntax for full outer join is as shown below:
In the above example when we apply full outer join, the results will be displayed as:
The output to the above full outer join query is:
| name | city |
|---|---|
| Meenakshi | Ajmer |
| Manjili | NULL |
| Sheetal | Bangalore |
| Apoorva | NULL |
| kitto | Udaipur |
| Nitin | Chittor |
| Soumya | NULL |
| NULL | Raipur |
As we can see from the output above, all the records from both tables are returned in the output table.
We can see the above image to understand how the full outer join is happening between tables.
How to Use OUTER JOIN in SQL?
The outer join works by selecting all the records from both tables. The left outer join returns all records from the left table and the matched records from the right table. The right outer join returns all records from the right table and the matched records from the left table. The full outer join returns all records when there is a match in either left or the right table.
Learn More
Conclusion
-
Joins in SQL: Used to merge data from two or more tables based on common columns.
-
Inner Join:
- Returns only the rows with matching values in both tables.
- Focuses on common rows and is more restrictive, which might lead to data loss due to condition constraints.
- Generally slower due to its need to find precise matches.
- Outer Join:
- Encompasses Left, Right, and Full Outer Join.
- Left Join: All records from the left table and matching ones from the right.
- Right Join: Opposite of Left Join.
- Full Outer Join: All records when there’s a match in either table. Also known as a cross join.
- Faster and offers a more comprehensive view of merged tables.
- Comparison:
- Inner Join is a straightforward merge, while Outer Join types can vary.
- Performance is typically better with Outer Joins as they’re less restrictive.