In this article, let’s discuss the difference between SQL Inner Join and Outer Join.
A Join clause helps the user to combine values from two or more tables based on a correlation between them. The relation between them is typically lying on the columns of Table A and Table B that have matching values.
2.1. Uses of Joins
In a relational database, data is distributed in many related tables. Join Clause helps the programmer to obtain the data from more than 1 table by using the values common to each. It pieces the data together and makes it useful and understandable. In most cases, it matches the column value of one table with another table.
3. Inner Join
Inner Join is also called as EQUIJOIN. This matches the left table to the right table. It retrieves the data by combining the values of two tables. This Join returns the rows from table 1 that matches with the rows from table 2. The column values of matched rows of the two tables are combined into a result row.
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;
4. Outer Join
An Outer Join returns a set of records that includes what an Inner Join would but also includes other rows for which no match is found.
4.1. Full Join
Full Outer Join or Full Join. This Join not only return the rows that are matched, but also the rows which are mismatched. These mismatched sides will have NULLS.
SELECT table1.column1, table2.column2... FROM table1 FULL JOIN table2 ON table1.common_field = table2.common_field;
4.2. Left Outer Join
A Left Outer Join will return all the data in Table 1 and also the shared data that the Table 2 had in common.
SELECT table1.column1, table2.column2, FROM table1 LEFT OUTER JOIN table2 ON table1.common_field = table1.common_field2;
4.3. Right Outer Join
A Right Outer Join is a reverse form of Left Outer Join where it returns all the data in Table 2 and also the data Table 1 had in common.
SELECT table1.column1, table2.column2, FROM table1 RIGHT OUTER JOIN table2 ON table1.common_field = table1.common_field2;
5. Differences Between Sql Inner Join and Outer Join
|Inner Join||Outer Join|
|Inner Joins don’t tweak non-matching rows||Outer Joins includes both matching and non-matching rows in the result set|
|No types of Inner Join||Three types of Outer Join|
Left Outer Join
Right Outer Join
Full Outer Join
|Size of the results returned by the Inner Join is comparatively smaller than Outer Join||Outer Join returns larger database|
To summarize, we have the differences between SQL Inner Join and Outer Join.