An inner join essentially combines the records from two tables A and B based on a given join predicate. The cross product of all records in table is computed. Thus, each record in table A is combined with every record in table B. Only those records in the joined table that satisfy the join predicate remain. This is the most common type of join used in applications, and is considered the default join type.
An equi-join is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join predicate. Using other comparison operators such as
< disqualifies a join as equi-join. The query 2 sections above is already an example for equi-joins.A natural join is a further specialization of equi-joins. The join predicate is implicitly given by comparing all columns in both tables that have the same column name in the tables to be joined. The resulting joined table contains only one column for each pair of equally-named columns.
Outer joins do not require that each record in the two joined tables has a matching record in the other table. The record is retained in the joined table if no other matching record exists. Outer joins are subdivided further into left outer joins, right outer joins, and full outer joins, depending from which table the rows shall be retained (left, right, or both).
The result of a left outer join for tables A and B always contains all records of the "left" table (A), even if the join condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, a row in the result will still be returned — but with NULL in each column from B.
A right outer join is much like a left outer join, except that the tables are reversed. Every record from the "right" table (B) will be in the joined table at least once. If there is no matching row from the "left" table (A), and NULL will be used for columns from A for those records that have any match in A.
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
