Relational databases have collections of data, usually called tables. Join query statements are used to link these collections.

Most common joins are CROSS JOIN, (INNER) JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN.

CROSS JOIN is the same as mathematical cartesian product. It returns every permutation of rows from the two tables thus it doesn't have an predicate.

Other joins have an predicate which defines what aspects of the data e.g. columns are compared in join operation.

INNER JOIN evaluates the condition in the ON clause for all rows in the cross join result. If true return the joined row. Otherwise discard it.

LEFT JOIN is the same as inner join but for any rows in the left table that did not match anything output these with NULL values for the right table columns.

RIGHT JOIN is the same as inner join but for any rows in the right table that did not match anything output these with NULL values for the left table columns.

FULL JOIN is the same as inner join but preserves left non matched rows as in left outer join and right non matching rows as per right outer join.

LEFT JOIN, RIGHT JOIN, FULL JOIN are always OUTER joins.

Usage:

• INNER JOIN is good when starting and joined table are required.
• LEFT JOIN is good when the joined table is optional data.
• RIGHT JOIN is good when the start table is optional data.
• FULL JOIN is good when start and joined table are both optional data.

Most used joins are LEFT JOIN and INNER JOIN.

Sources

• Difference between INNER and OUTER joins