Database Related

In SQL, What’s The Difference Between A Full Join And An Inner Join?

Before we move on to the actual differences between a full join and an inner join, we need to understand what exactly a join is. Joins, as the name suggests, are used to combine data of two different tables into a new temporary table. The resultant table is created on the basis of the column(s) that match in the two tables. This new and temporary table formed by the join is then used for the comparison of meaningful data. A join is performed on a predicate – a condition that specifies when to perform a join.

Inner Join:

An inner join makes use of comparisons in both tables before combining the data together. In an inner join, the two tables that are to be joined need to have equal records. Considering the tables that are to be joined to be table X and table Y, an inner join generates the new temporary table Z by combining each matched value of X and Y. The comparison is made according to a join predicate that defines the rules for comparison. The join query matches every row of X with each corresponding row of Y to find pairs. Once a non-NULL value is found to have matched, the values of both rows in X and Y are combined to form a row in Z. Though it is the common choice for a join operation, an inner join is not best suited for all applications.

Full Outer Join:

A full outer join is effectively the complete opposite of an inner join. A full join combines together all the records of the first table, Table A and the second table, Table B irrelevant of the join-predicate being satisfied or not. Therefore, in a full outer join, the new result table always contains all records of the two tables joined together. In the full outer join result table, for each set of records that do not match in table A and table B, the resultant row has a NULL value for the particular column that does not match. For the records that don’t match, a full outer join has a single resultant row that contains fields from both tables A and B.

Leave a Reply