- Inner joins match values in all tables
- Outer joins match even if one table does not have a match
- Equijoins do an exact match on values
- Non-equijoins match with comparisons such as "greater than"
Another way to join tables with same column names is to use USING(same_col_name). You cannot prefix the column name with an alias or even a table name. This is similar to the natural join, except that this allows outer joins as well as inner joins.
The self join will join a table with itself. It can be an inner join or an outer join. It can be an equijoin or a non-equijoin.
Finally there is the Cartesian product. It is also called the cross join. This is not very useful. You invoke it with the CROSS JOIN syntax, or SELECT FROM multiple tables without specifying any joins. Be warned that you might get a whole lot of rows with this option.