Inner Join vs Outer Join
Inner join and Outer join are two of the SQL joining methods used in query processing for databases. They belong to the family of join clauses (other two being Left and Right Joins). However, there is a Self Join which can be used for specialized situations. The purpose of Join is to combine fields using the common values to the two tables. These joins combine records from multiple tables in a database. It creates resultant sets, which can be saved as another table.
What is Inner Join?
Most commonly used SQL Join operation is the Inner Join. It can be considered as the default type of join used in applications. Inner join use the join-predicate to combine two tables. Assuming the two tables are A and B, then the join-predicate will compare rows of A and B to find out all the pairs which satisfy the predicate. Column values of all satisfied rows of A and B tables are combined to create the result. It can be looked at as first taking the cross join (Cartesian product) of all records and then returning only the records that satisfy the join-predicate. However, in reality, Cartesian product is not calculated because it is very inefficient. Hash join or sort-merge join is used, instead.
What is Outer Join?
Unlike, Inner join, outer join keeps all the records even if it can’t find a matching record. That means outer join does need a record to find a matching record for it to appear in the result. Instead, it will return all the records, but unmatched records will have null values. Outer joins are divided in to three sub categories. They are left outer join, right outer join and full outer join. This differentiation is based on which table’s (left table, right table or both tables) row is retained when unmatched records are found. Left outer joins (also known as simply left join) retains all records of the left table. That means, even if the number matching records is zero, then it will still have records in the result table, but will have null values for all columns of B. In other words, all values from left table are returned with matched values from right table (or null values when unmatched). If values from multiple rows from left table are matched with a single row from right table, the row from right table will be repeated as required. Right outer join is pretty much similar to left outer join, but treatment of tables is revered. That means the result will have all the rows of right table at least once with matched left table values (and null values for unmatched right values). Full outer join is more comprehensive than both left and right outer joins. It results in combining the effect of applying both left and right outer joined together.
What is the difference between Inner Join and Outer Join?
Inner Join does not keep the unmatched rows in the result, but outer join will keep all the records from at least one table (depending on which outer join has been used). So, the behavior of having no information present on unmatched rows in the result table is undesirable, you need to always use one of the outer joins (in place of inner join). Inner join may not produce a result if no matches are found. But outer join will always produce a resultant table, even with no matching rows. Inner join will always return tables with values (if returned). But outer joins may result in tables with null values.