All About Joins

There are multiple ways to classify SQL joins:
  • 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"
There are three types of outer joins:
  1. Left
  2. Right
  3. Full
A natural join will join multiple tables that have the same column names. You cannot provide aliases for the joined columns. This produces an inner join.

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.