To comprehend the difference between the two types of joins i.e. inner join and Outer join in SQL, let’s first revise the basic knowledge about the join in SQL, the inner join and the outer join.
What is a join in SQL?
A join in SQL may be defined as a way which is used to combine and compare two or more than two tables or literally join them to return the desired data rows from these tables.
- Read: More Interview Questions
What is an inner join in SQL?
An Inner join is used when we need to focus on the common data items. It is used when the tables (two or more) which we desire to compare have some kind of matching data stored in them. The inner join searches for the matching data and returns it in a new table. For example, let’s take two tables Product_type and Product_Price. In both the tables the common column will be of product name. So, its best and logical to apply join on product name (column). There will be some values of the column product name which are common in both the tables and some values which are different in both the tables and when we use Inner join on these tables the resultant table will consist all the values or product names which are common in both the tables.
What is an outer join?
The outer join in SQL returns data or records which are matching in all the tables and also the records or rows which don’t have a match in other table. Basically the outer join can be categorized in three sub types and they are:
Left outer join: This join is also known as Left join. This join when used on the tables in SQL, it returns all the data or records from the table on left side and with the matching data from the other table (or tables) i.e. the table on right side.
- Do you Know: SQL Clustered & Non-clustered Index
Right Outer Join: This join is also known as right join. As, the name suggests this join when used on the tables in SQL, returns all the records or data rows of the right table with the matching records on the table on left.
Full outer join: It is also known as full join. This join when applied on the Tables in SQL, it returns all the data from the tables it is applied on. It doesn’t matter whether there are matching records present in the tables or not, the full join will duplicate all the records or data rows from the tables and make a new table with the entire data in it.
Inner and Outer both types of joins are used to combine desired data rows from various tables into one resultant table. These joins work on the Join condition which specifies the type of data required in the resultant table. This join statement or condition is critical as it defines about how many columns will participate from each table to get matching results.
- Recommended: Final year Academic projects download
Basic Difference between the Inner and Outer Joins in SQL!
From above, we came to know that in the case of both the joins the resultant table will have the data rows which are matched according to the Join condition but the difference of these joins lie in the fact as how they handle when a false matching condition comes in the way?
When a false match occurs, the Inner join doesn’t include non-matching data rows whereas Outer join includes these non-matching data rows in its final resultant table.