SQL join
A SQL JOIN clause is a means for combining records from two or more tables in a database by using values common to each.ANSI standard SQL specifies several types of Joins: INNER, OUTER, LEFT, and RIGHT, full join.
There are also some derivations, such as self-join, cross join.
Inner Join
Inner join is the default join-type, it only return rows with matching values from both joined tables excluding all other rows.
Outer joins
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).
The whole idea behind an outer join is to retrieve all rows from table A (left) or table B (right), even though there are no matching columns in the counterpart table. A left (or right) outer join also returns nulls for all unmatched columns from the joined table.
Left outer join
The result of a left outer join (or simply left join) for table 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, the join will still return a row in the result—but with NULL in each column from B.
This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).
If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table.
Right outer joins
A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.
A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
The only difference between left and right outer joins is the order in which the tables are joined in the query, and they can always be replaced with left outer joins.
Full outer join
A full outer join combines the results of both left and right outer joins. It returns all rows from both "left" and "right" tables, no matter if the counterpart table has matching rows or not, and fill in Nulls for missing matches on either side.
Other kinds of Join
Natural join
Natural-join joins tables by column(s) with identical names. we don't have to specify the columns explicitly in the ON subclause of the SELECT statement's FROM clause. The ON subclause is omitted completely.
Self-join
A self-join is joining a table to itself.
One important thing to understand is that despite the fact that we are joining the table with itself, we are still dealing with two instances of the same table, or with two identical tables rather than with just one table.
Cross join (Cartesian product)
A cross join combines every row in B with every row in A. The number of rows in the result set will be the number of rows in A times the number of rows in B. Thus, if A and B are two sets, then the cross join is written as A × B.
Example
Employee and Department Table
Resources:
Join (SQL)
SQL Bible
