Relational databases have collections of data, usually called tables. Join query statements are used to link these collections.
Most common joins are
RIGHT JOIN and
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.
FULL JOIN are always
INNER JOINis good when starting and joined table are required.
LEFT JOINis good when the joined table is optional data.
RIGHT JOINis good when the start table is optional data.
FULL JOINis good when start and joined table are both optional data.
Most used joins are
LEFT JOIN and