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

TL;DR:

• INNER JOIN: both the start and joined table are required
• LEFT JOIN: the joined table has optional data
• RIGHT JOIN: the start table has optional data
• FULL JOIN: start and joined table both have optional data

Most used joins are LEFT JOIN and INNER JOIN.

The usually available 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 a predicate.

Other joins have a 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 collectively OUTER joins.

Sources

• Difference between INNER and OUTER joins