Just a minor addition regarding types of joins: the primary dichotomy is between what are called inner and outer joins. Inner joins are more or less joins which return matching records which must exist in both "tables" (or "sides" of the join, really; they can also be sub-selects and stuff, but to keep it simple, think of two tables). Outer joins are where records from one don't have to match records in the other. This is where the "RIGHT" and "LEFT" join dichotomy layer comes into play. Basically, it allows you to say which table on the side of the join (LEFT or RIGHT) will have non-matching records pulled from it.

Here are some examples to differentiate them more clearly:

Table1: (fields are key|id_number|name)
A|123|Fred
B|765|Joe
D|901|Mack

Table 2: (fields are key|color|time)
A|Red|01:30
C|Blue|17:45
D|Cyan|10:11

Now, let's do an inner join: (explicit inner join)
SELECT Table1.key, Table1.name, Table2.color FROM Table1 INNER JOIN Table2 ON Table1.key=Table2.key
-or- (the implied inner join)
SELECT Table1.key, Table1.name, Table2.color FROM Table1, Table2 WHERE Table1.key=Table2.key

Here's what we would get: (fields are key|name|color)
A|Fred|Red
D|Mack|Cyan

Next, let's do a left outer join:
SELECT Table1.key, Table1.name, Table2.color FROM Table1 LEFT OUTER JOIN Table2 ON Table1.key=Table2.key

Here's what we would get: (fields are key|name|color)
A|Fred|Red
B|Joe|(null)
D|Mack|Cyan

Next, let's do a right outer join:
SELECT Table1.key, Table1.name, Table2.color FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.key=Table2.key

Here's what we would get: (fields are key|name|color)
A|Fred|Red
C|(null)|Blue
D|Mack|Cyan

[hr]

You can omit the "OUTER" keyword on an outer join, and just specify it "LEFT JOIN" or "RIGHT JOIN". "LEFT"/"RIGHT" are by default outer joins.

Seems simple, no? It can get quite complicated in short order when you are joining multiple tables and doing aggregate functions.

If you're really geeky about set theory, joins are different flavors of intersection.