Expanding on
psydereal's explanations above...
Joins are a powerful part of relational algebra in database theory.
Cartesian Product (R1 × R2)
Technically, Cartesian Products are not considered true joins, but they are similar enough to warrant mentioning. The Cartesian product concatenates every tuple in the first relation with every tuple in the second relation. This includes illogical concatenations, limiting the usefulness of a raw Cartesian Product. However, the Cartesian Product is the primitive from which more useful joins are born.
Theta join (R1 |X|predicate R2)
The tuples in a Cartesian product of two relations satisfying some predicate making use of any comparison sign (= ≠ < > ≤ ≥). This is the type of join that is signified with the bow tie shape that cannot currently be represented in HTML.
Example:
NoderInfo |X|NoderInfo.XP < NoderWriteups.totalRep NoderWriteups
would give you all the NoderInfo and NoderWriteups table information for noders with less XP than the sum total of their writeup reputations.
Equijoin
An equijoin of a special case of the Theta join where the only comparison used in the predicate is =.
Natural join (R1 |X| R2)
A natural join is a special case of the Equijoin, where the two relations already have at least one field in common. The relations are joined, and redundant fields are ignored. Because a natural join means that there are common fields, the predicate is not explicitly stated, unlike other Theta joins.
Outer join (R1 ]X| R2)
Tuples from R1 that do not have matching values in the common attributes of R2 are also included in the result of the join. Missing values in the second relation are set to null.
Example:
NoderInfo ]X| NoderWriteups
would give you a list of all NoderInfo and all NoderWriteups for all noders, even if they haven't written any writeups yet.
Technically ]X| (looking sort of like a bowtie with flags on it) is a left, or natural, outer join, but |X[ and ]X[ can also be used, called a right outer join and full outer join, respectively.
Semijoin (R1 |>predicate R2)
A join containing the tuples of R1 that would be used in a theta join with R2. This can be rewritten as the projection of all attributes A in R1 of the theta join of the two relations:
πA(R1 |X|predicate R2)
Example:
NoderInfo |>NoderInfo.XP < NoderWriteups.totalRep NoderWriteups
would only give you NoderInfo for noders with less XP than the sum total of their writeup reputations.
As you can see, the semijoin symbol looks like an arrowhead pointing at the second relation, or half a bowtie.