Database Reference
In-Depth Information
Inner join.
Combines rows from both tables using matching column
names and column values. The result set includes only rows that
match.
Outer join.
Selects rows from both tables as with an inner join but
including rows from one or both tables that do not have matching
rows in the other table. Missing values are replaced with null values.
Left outer join.
All rows from the left table plus all matching
rows from the right table. Column values from the right table are
replaced with null values when the matching right-side row does
not exist in the left-side table.
Right outer join.
All rows from the table on the right plus match-
ing rows from the left table, the opposite of the left outer join.
Full outer join.
All rows from both tables, with null values
replacing missing values.
Self-join.
This joins a table to itself.
Equi-joins, anti-joins, and range joins.
An equi-join combines
table data based on equality (=), an anti-join matches data based on
inequality (!=, <> or NOT), and a range join compares data using a
range of values (<, > or BETWEEN).
Mutable and complex joins.
A mutable join is a join of more than
two tables. A complex join is a mutable join with added filtering.
This is a lot of technical jargon. Let's explain what joins are using simple
mathematical set theory. Relational database theory is based on set theory.
Do you remember learning set theory at school? A direct correlation can be
made between simple set theory and relational database joins.
Figure 10.3 shows two completely unrelated sets, Set A and Set B. That
is, the two sets are not related to one another in any way. You can see that
Figure 10.3
Two Unrelated
Sets.
