Joins

Joins basically combine 2 tables. The three most common are:
INNER JOIN: intersection between 2 sets
LEFT OUTER JOIN: all rows from left side, matching or NULL rows from right
RIGHT OUTER JOIN: same as left, just in reverse

The others are less common, and may not be available with all platforms:
FULL OUTER JOIN: all rows from left side, matching or NULL rows from right; plus all rows from right side, matching or NULL rows from left (basically, a UNION ALL between LEFT and RIGHT joins...in fact, if your DB doesn't support FULL joins, you can do that)
CROSS JOIN: all possible matches (the Cartesian product) between left and right side. This one is rarely useful, IME.

For an example, let's start with 2 simple tables:


Employee

[Id]     [Name]    [SalaryId]

1         Mark        2

2         Jim           1

3         Bob          6

4         Steve       NULL


Salary

[Id]     [Amount]

1         $100000

2         $80000

3         $70000

Okay, so to get the salary of each employee, we can do:


SELECT Employee.Name, Salary.Amount FROM Employee INNER JOIN Salary ON Employee.SalaryId = Salary.Id


[Name]    [Amount]

Mark        $80000

Jim           $100000

You can see that (a) we have to give a JOIN criteria to say what links the 2 tables together (in most cases, this will be your foreign key), and (b) neither Bob nor Steve were returned because their SalaryId is not in the Salary table.

If we wanted to see Bob or Steve in the result set, we'd need an OUTER JOIN:


SELECT Employee.Name, Salary.Amount FROM Employee LEFT OUTER JOIN Salary ON Employee.SalaryId = Salary.Id


[Name]    [Amount]

Mark        $80000

Jim           $100000

Bob          NULL

Steve       NULL

Now, we have Bob and Steve in the result set, but their Amounts are NULL because we don't know what they are. Notice now that we don't have our 3rd salary level, $70000, listed because no one is getting paid that.


SELECT Employee.Name, Salary.Amount FROM Employee RIGHT OUTER JOIN Salary ON Employee.SalaryId = Salary.Id


[Name]    [Amount]

Mark        $80000

Jim           $100000

NULL        $70000

Now, we have all of the salary amounts. If we want both all the names and all the salary amounts:


SELECT Employee.Name, Salary.Amount FROM Employee FULL OUTER JOIN Salary ON Employee.SalaryId = Salary.Id


[Name]    [Amount]

Mark        $80000

Jim           $100000

Bob          NULL

Steve       NULL

NULL        $70000

I can't think of many reasons to use a CROSS JOIN right now, but here's an example anyway:


SELECT Employee.Name, Salary.Amount FROM Employee CROSS JOIN Salary


[Name]    [Amount]

Mark        $80000

Mark        $100000

Mark        $70000

Jim           $80000

Jim           $100000

Jim           $70000

...

As you can see, we didn't have to give a JOIN criteria, because a CROSS JOIN makes all possible combinations. I didn't fully list out the result set, but you can see that it'll continue that pattern for all employees.

To be honest, I think I have used a CROSS JOIN in one or two instances, but it's pretty rare and I can't think of why I did it right now.