Most of the
time when you are query data, you will need to retrieve data from multiple
tables. To connect them you will need to employ a JOIN. There are multiple
types of joins depending on your needs. One rare type is the CROSS JOIN. This
produces a Cartesian product of two tables. That means every row in the first
table is paired with every row in the second table. If the first table has m
rows, and the second has n rows, the CROSS JOIN will produce m times n number
of rows in the results.
The most
common type of JOIN is the INNER JOIN. This is the default JOIN type. It will
only connect rows in the first and second tables when there is a match with the
values of the columns you are joining on.
Another common type of JOIN is the LEFT OUTER
JOIN. This is also known as the LEFT JOIN. This will produce results that have
all of the rows from the first (left) table, plus any rows in the second table
that have matching values in the column you are joining on. Related is the
RIGHT OUTER JOIN. This is like a LEFT OUTER JOIN. But the roles of the tables
are reversed. You get all rows from the second (right) table, plus any in the
first table that match.
