🔀 JOINs
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 requiredLEFT JOIN
: the joined table has optional dataRIGHT JOIN
: the start table has optional dataFULL JOIN
: start and joined table both have optional data
Most used joins are
LEFT JOIN
andINNER 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 collectivelyOUTER
joins.