Lost Password?


Go Back   CodeCall Programming Forum > Web Development Forum > Database & Database Programming

Database & Database Programming MySQL, Oracle, SQL, PL/SQL, ABAP, Smart Forms, and other databases and languages. A database is an organized body of related information used in many websites (including CC).

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 07-20-2006, 11:43 AM
NeedHelp NeedHelp is offline
Programming God
 
Join Date: May 2006
Posts: 527
Rep Power: 13
NeedHelp is on a distinguished road
Default Joins

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?
__________________
I Need Help
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote

Sponsored Links
  #2 (permalink)  
Old 07-23-2006, 07:46 PM
brackett brackett is offline
Programmer
 
Join Date: May 2006
Posts: 193
Rep Power: 11
brackett is on a distinguished road
Default

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:
Code:
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:
Code:
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:
Code:
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.
Code:
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:
Code:
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:
Code:
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.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 08-02-2006, 06:35 PM
Saint Saint is offline
Learning Programmer
 
Join Date: Aug 2006
Posts: 63
Rep Power: 9
Saint is on a distinguished road
Default

Good description. Joins are very common in SQL.
__________________
Hi >> Saint
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump


All times are GMT -5. The time now is 12:04 PM.

Contest Stats

WingedPanther ........ 2753.6
Xav ........ 2704
Brandon W ........ 1702.32
John ........ 1207.73
marwex89 ........ 1175.24
morefood2001 ........ 966.05
dcs ........ 655.75
Steve.L ........ 475.59
orjan ........ 418.58
Aereshaa ........ 383.54

Contest Rules

CodeCall Goal

Goal: 100,000 Posts
Complete: 98%

Ads