Joins

[NeedHelp]

Can someone explain to me what joins are, how to use them and why you would use them? I have heard of an inner join but have no idea what it is. Is this the only one?

[brackett]

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.

[Saint]

Good description. Joins are very common in SQL.